question

DaneBriggs-5625 avatar image
0 Votes"
DaneBriggs-5625 asked DaneBriggs-5625 commented

Hashtable Export is unexpectantly being trimmed

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 AD`enter 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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So I tested by manually removing all users from the CSVs that are part of different domains. It paired 1 CSV from just under 300 to just under 800 lines. It paired another CSV from under 4500 to 1400 it works, with the exception it still isn't tagging the users that aren't in AD. Am I hitting some sort of limit?

0 Votes 0 ·

1 Answer

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered DaneBriggs-5625 commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That makes sense why the export is working as expected!

Lines 141 and 143 was just me testing different outputs. Just forgot to comment one out.


0 Votes 0 ·