Hashtable Export is unexpectantly being trimmed

Dane Briggs 211 Reputation points
2022-05-23T18:26:39.893+00:00

I've written a script that takes an HR csv, hardware inventory csv, O365 license export and AD. Combines them together to get a report. Some users will be in the domain while others will not. For the user's that are not in this specific domain I want set the hashtable value samaccount name to "Not in AD"

Hashtable Expected Output of users with AD accounts and laptop\desktop
{EEID, LastName, FirstName, MI, Job, JobDescription, SamAccountName, EmployeeID, EmployeeNumber, LastLogonDate, Enabled, Licenses, UPN, Type}

When I run my script I am getting 3 different results in my hashtable $final

  • Doe*John {EEID, LastName, FirstName, MI, Job, JobDescription, SamAccountName, EmployeeID, EmployeeNumber, LastLogonDate, Enabled, Licenses, UPN} - User from HR file that is in ADenter code here
  • Blow*Joe {EEID, LastName, FirstName, MI, Job, JobDescription, SamAccountName, EmployeeID, EmployeeNumber, LastLogonDate, Enabled, Licenses, UPN, Type} - User in HR file that is in AD and has a laptop/desktop
  • Doe*Jane {EEID, LastName, FirstName, MI, Job, JobDescription} - User in HR file that is not in AD (Expected samaccount = Not in AD)

When I export my results to a CSV I only get EEID, LastName, FirstName, MI, Job, JobDescription. For the users that are not in the Domain when I do a $final am I not seeing samaccount = Not in AD? Why is my export being truncated?

Script below

######################################################################################
# Varaibles 
######################################################################################
Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope Process -Force
$PSScriptRoot

$InputPath = "C:\Users\me\OneDrive\WorkInProgress\AutomateReport"

cd $InputPath 
$ProccessedPath = "$InputPath\Proccessed"
$OutputPath = "$InputPath\Results"
$logfolder = "$InputPath\Logs"
$Date = Get-Date -format "yyyy-MM-dd"
$Logfile = "$logfolder\$Date AutomateReport.log"


######################################################################################
# Create log File 
######################################################################################

Test-Path -Path $Logfile
#If the file does not exist, create it.
if (-not(Test-Path -Path $Logfile -PathType Leaf)) {
     try {
         $null = New-Item -ItemType File -Path $logfile -Force -ErrorAction Stop
         Write-Host "The file [$Logfile] has been created." 
     }
     catch {
         throw $_.Exception.Message
     }
 }
# If the file already exists, show the message and do nothing.
 else {
     Write-Host "$logfile already exists"
     Remove-Item $Logfile
     $null = New-Item -ItemType File -Path $logfile -Force -ErrorAction Stop

 }
######################################################################################
# Inputs
######################################################################################

cd $InputPath
$HRInput = (Get-ChildItem ".\*HR*.csv").Name | import-csv | select -Property @{label="EEID";expression={$($_."EE ID")}}, @{label="LastName";expression={$($_."Last Name")}},@{label="FirstName";expression={$($_."Preferred Name")}},@{label="MI";expression={$($_."Middle Initial")}},@{label="Job";expression={$($_."Job")}}, @{label="JobDescription";expression={$($_."Job Description")}}
$UO365Input = (Get-ChildItem ".\*users_*.csv").Name | import-csv | select -Property @{label="LastName";expression={$($_."Last Name")}},@{label="FirstName";expression={$($_."First Name")}},@{label="Licenses";expression={$($_."Licenses")}},@{label="UPN";expression={$($_."User principal name")}} | where {$_.UPN -like '*contoso.com'}

######################################################################################
# HW data manipulation
######################################################################################

$HWInput = (Get-ChildItem ".\*Username to LaptopDesktop *.csv").Name | Import-Csv | Select -Property @{label="samaccountname";expression={$($_."Short Name")}}, @{label="Type";expression={$($_."Type")}},@{label="Name";expression={$($_."Name")}} | where {$_.samaccountname -like 'contoso\*'}   
foreach ($sa in $HWInput){
    $SAM=$sa.samaccountname
    $SAM = $SAM.TrimStart('contoso').TrimStart("\")
    $sa.samaccountname = $SAM

    $fn = get-aduser -filter "samaccountname -like '$($SAM)'" -Properties GivenName | select GivenName -ExpandProperty GivenName
    $ln = get-aduser -filter "samaccountname -like '$($SAM)'" -Properties surname | select surname -ExpandProperty surname
    $IName = $ln,$fn -join "*"   # unlikely to find asterisk in names
    $sa.Name = $IName

        $Type=$sa.Type
            If ($Type -like "portable"){$Type = "Laptop"
        $sa.Type = $Type
            } ElseIf ($Type -like "workstation"){$Type = "Desktop"}
            $sa.Type = $Type
        } 

 ######################################################################################
 # build HW hashtable - Determines Type of equipment (Laptop, Desktop, Both)
 ######################################################################################
$HWip = $HWInput[0].psobject.properties.name
$HT = @{}
Foreach ($Il in $HWInput){
    $key = $Il.name
    $Value = $Il.Type
    If ($HT.ContainsKey($key)){
         Write-Host "Found duplicate: $key" 
    Continue 
    }
     $ht.add($key,$Value)

     }

 Foreach ($Il in $HWInput){ 
    $key = $Il.name
    If (($HT[$key] -like "both") -or ($HT.$key -like $Il.Type)){
    write-Host "No Change"            
    }Else{
        $HT[$key] ='Both'
        write-Host "Both"}
    }

# Changes hastable to an array
$hto = $ht.GetEnumerator() | Sort-Object name

$hto = $hto |  Select -Property @{label="Name";expression={$($_."Name")}}, @{label="Type";expression={$($_."Value")}}

 ######################################################################################
 # build the initial hash
 ######################################################################################

 # begin the aggregation
 $HRInputprops = $HRInput[0].psobject.properties.name
 $final = @{}
 ForEach ($l in $HRInput){
     $key = $l.LastName,$l.FirstName -join "*"   # unlikely to find asterisk in names
     if ($final.ContainsKey($key)){
         Write-Host "Found duplicate in Employee Export HR: $key" 
         "Found duplicate in Employee Export HR: $key" | out-file $Logfile -Append
         Continue
     }
     $emp1 = [ordered]@{}
     foreach ($p in $HRInputprops){
         $emp1[$p] = $l.$p
     }
     $final[$key] = $emp1

 }
 ######################################################################################
 # add the Licenses and UPN to the hash based on matching "LastName*FirstName" keys
 ######################################################################################
 ForEach ($UO365Inputl in $UO365Input){

     $key = $UO365Inputl.LastName,$UO365Inputl.FirstName -join "*"
     if (-NOT $final.ContainsKey($key)){
         Write-Host "Didn't find '$key' from O365Input in HRInput" 
         "Didn't find '$key' from O365Input in HRInput" | out-file $Logfile -Append
         Continue
     }


 ######################################################################################
 # Add AD data to the has based on the matching UPN Keys
 ######################################################################################
     $UPN = $UO365Inputl.UPN
     $ln=$UO365Inputl.LastName
     $fn=$UO365Inputl.FirstName
     $u = get-aduser -filter {UserPrincipalName -eq $UPN}  -properties  SamAccountName, EmployeeID, EmployeeNumber, LastLogonDate,Enabled | select  SamAccountName, EmployeeID, EmployeeNumber, LastLogonDate, Enabled
     if ($u -eq $null){
         $final[$key]["SamAccountName"] = "$key Not in AD"
         Write-Host "Failed to find user '$($UO365Inputl.UPN)' in AD"
         $final[$key]["SamAccountName"] = "Not in AD" 
         "$key $UPN not in AD" | out-file $Logfile -Append


     }
     else{
         $final[$key]["SamAccountName"] = $u.SamAccountName
         $final[$key]["EmployeeID"] = $u.EmployeeID
         $final[$key]["EmployeeNumber"] = $u.EmployeeNumber
         $final[$key]["LastLogonDate"] = $u.LastLogonDate
         $final[$key]["Enabled"] = $u.Enabled

         }

     $final[$key]["Licenses"] = $UO365Inputl.Licenses
     $final[$key]["UPN"] = $UO365Inputl.UPN
     }


 ######################################################################################
 # Add HW data to the hash based on the matching "LastName*FirstName" keys
 ######################################################################################  

   ForEach ($lhto in $HTO){
     $key = $lhto.name
     if (-NOT $final.ContainsKey($key)){
         Write-Host "Didn't find '$key' From HW Export in HRInput" 
         "Didn't find '$key' From HW Export in HRInput" | out-file $Logfile -Append
         Continue
         $final[$key]["Type"] = $lhto.Type
     }

     $final[$key]["Type"] = $lhto.Type
     }



######################################################################################
 # Finalize the Hashtable and write to csv
 ######################################################################################
 $final.keys |
     ForEach-Object{
         [PSCustomObject]$final.$_ | export-csv -NoTypeInformation -Force $OutputPath\$date-AutomateReport.csv  -append
     }


 ######################################################################################
 # Move Proccessed files
 ######################################################################################     

 $ProcFiles = Get-ChildItem -Path $InputPath -filter *.csv
 ForEach ($PF in $ProcFiles) {
    Move-Item $PF -Destination "$ProccessedPath\$Date - $PF"

    }

 ######################################################################################
 # File Cleanup - 30 days or older
 ######################################################################################   
  Get-ChildItem -Path $ProccessedPath | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-30))} | Remove-Item | out-file $Logfile -Append
  Get-ChildItem -Path $OutputPath | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-30))} | Remove-Item | out-file $Logfile -Append
  Get-ChildItem -Path $logfolder | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-30))} | Remove-Item | out-file $Logfile -Append
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,363 questions
Accepted answer
  1. Rich Matheisen 44,776 Reputation points
    2022-05-23T19:56:21.857+00:00

    Without going over your script in detail, the key to getting a consistent CSV file is to ensure you always populate the hash with ALL the keys.

    For example, lines 140 - 153: You never add the keys EmployeeID, EmployeeNumber, LastLogonDate, and Enabled if the user isn't found in the AD.

    Just sticking to those few lines, what's up with lines 141 and 143? Do you want the "$key Not in AD" or "Not in AD" in the value assigned to the SamAccount key?

    When you populate the PSCustomObject with the data in the hash and the export it to the CSV, the CSV will have "ragged" rows. The Export-CV will build the CSV based on the contents of the first object in the pipeline (unless you provide a -Header prarameter). If that object has fewer properties than the 2nd then the 2nd will have only the properties in the SSV that existed in the 1st.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest