Powershell: Searching array from imported CSV data using a For-each loop

Question

question

DaneBriggs-5625 asked May 4, '22 RichMatheisen-8856 answered May 4, '22

Powershell: Searching array from imported CSV data using a For-each loop

I have been tasked to write a powershell script that will take the data from 2 CSVs and information from Active Directory and send the output to a separate CSV. The unfortunate part is that there is no unique single column for each of the CSVs and I will need to query AD using first name and last name. The only consistent data across all 3 is the first name and last name.

I think it comes down to two questions.

How do I merge the 2 CSVs using first and last names to a third CSV?
How do I query AD using both the first name and last name and add that information to the third CSV? (I don't want to use "get-aduser -filter * | where" because the Domain is huge and so are the CSVs. )

CSV Data is structured Like this

CSV1

CSV2

Wanted output

EDIT
I see what I was doing wrong with querying AD with the multiple filters.

I was trying to use $row.lastname and $row.firstname as my filter instead of setting a variable.

$CSV1 = import-csv .\CSV1.csv
ForEach ($Row in $CSV1){
$ln=$row.LastName
$fn=$row.FirstName
get-aduser -filter {sn -eq $ln -and GivenName -eq $fn} | select UserPrincipalName
}

windows-server-powershell windows-active-directory

image.png (5.4 KiB)

image.png (4.5 KiB)

image.png (11.2 KiB)

5 |1600 characters needed characters left characters exceeded

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

Answer 1 · 2022-05-04T19:07:17.543Z

RichMatheisen-8856 answered May 4, '22

See if this works for you:

 # just sample data -- to be replaced by CSVs
 $csvone = @"
 EEID,LastName,FirstName
 1,Smith,Joe
 2,Moore,Steve
 3,Blow,Joe
 "@
 $csvtwo = @"
 LastName,FirstName,JobCode
 Moore,Steve,101
 Smith,Joe,100
 Blow,Joe,102
 "@
 $CSV1 = $csvone | ConvertFrom-Csv   # substitute Import-CSV here
 $CSV2 = $csvtwo | ConvertFrom-Csv   # substitute Import-CSV here
    
 # begin the aggregation
 $csv1props = $csv1[0].psobject.properties.name
 $final = @{}
 # build the initial hash
 ForEach ($l in $CSV1){
     $key = $l.LastName,$l.FirstName -join "*"   # unlikely to find asterisk in names
     if ($final.ContainsKey($key)){
         Write-Host "Found duplicate in CSV1: $key"
         Continue
     }
     $emp1 = [ordered]@{}
     foreach ($p in $csv1props){
         $emp1[$p] = $l.$p
     }
     $final[$key] = $emp1
 }
 # add the JobCode to the hash based on matching "LastName*FirstName" keys
 ForEach ($l in $CSV2){
     $key = $l.LastName,$l.FirstName -join "*"
     if (-NOT $final.ContainsKey($key)){
         Write-Host "Didn't find '$key' from CSV2 in CSV1"
         Continue
     }
     $final[$key]["JobCode"] = $l.JobCode
     $u = get-aduser -filter {sn -eq $($l.LastName) -and GivenName -eq $($l.FirstName)}
     if (-NOT $u){
         Write-Host "Failed to find user '$($l.LastName) $($l.FirstName)' in AD"
         $final[$key]["UPN"] = "UNKNOWN"    
     }
     else{
         $final[$key]["UPN"] = $u.UserPrincipalName
     }
 }
 $final.keys |
     ForEach-Object{
         [PSCustomObject]$final.$_
     }

5 |1600 characters needed characters left characters exceeded

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

question