I'm trying to populate an employee ID column in a CSV file by querying Active Directory against another column in the CSV file called "OwnerEmail" (Which is the user principal name). The problem is users in the owneremail column do not all belong to the same domain. How can I query 2 domains at one time?

Table for reference

This is what I have tried so far. The script isn't working and there are no error messages. Any Ideas

$Domains='us.domain.corp', 'uk.domain.corp'


$CSVImport |Select-Object @{
        Name = "employeeID"
        Expression = { 
            foreach($user in $CSVImport) 
            {
                foreach($Domain in $Domains){

               $user= (Get-ADUser -Filter "UserPrincipalName -eq '$($_.OwnerEmail)'" -Server $Domain -Properties 'SamAccountName').SamAccountName
            
            }
            
            }
            }}, * |Select-Object employeeID, DepartmentNumber, OwnerEmail, @{Name="Costs"; Expression={"$ $($_.Cost)"}} | Export-Csv "$Env:temp/$OutputFile" -NoTypeInformation  

CodePudding user response：

How can I query 2 domains at one time?

There is no need to do this, you can query both at once with multithreading but seems like an overkill. What I would recommend is to query all users at once per Domain, the code below may seem awfully complicated but should be pretty efficient. See the inline comments for details.

# Import the Csv
$CSVImport = Import-Csv path\to\thecsv.csv

# Create a LDAP Filter to query all users at once
# This filter would look like this for example:
# (|([email protected])([email protected]))
$filter = "(|"
foreach($email in $CSVImport.OwnerEmail) {
    if(-not [string]::IsNullOrWhiteSpace($email)) {
        $filter  = "(userPrincipalName=$email)"
    }
}
$filter  = ")"

# For each Domain, use the same filter and get all existing users
'us.domain.corp', 'uk.domain.corp' | ForEach-Object { $map = @{} } {
    foreach($user in Get-ADUser -LDAPFilter $filter -Server $_) {
        # and store them in a hashtable where
        # the Keys are their `UserPrincipalName`
        # and the Values are the attribute of interest (`SamAccountName`)
        $map[$user.UserPrincipalName] = $user.SamAccountName
    }
}

# Now we can simply use a calculated property with `Select-Object`
$CSVImport | Select-Object @{N='EmployeeID'; E={ $map[$_.OwnerEmail] }}, * |
    Export-Csv "$Env:temp/$OutputFile" -NoTypeInformation