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

Dane Briggs 211 Reputation points
2022-05-04T17:22:16.807+00:00

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.

  1. How do I merge the 2 CSVs using first and last names to a third CSV?
  2. 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
198933-image.png

CSV2
198942-image.png

Wanted output
198838-image.png

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
}

Active Directory
Active Directory
A set of directory-based technologies included in Windows Server.
6,231 questions
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,459 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rich Matheisen 45,831 Reputation points
    2022-05-04T19:07:17.543+00:00

    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.$_
        }
    
    0 comments No comments

0 additional answers

Sort by: Most helpful