CODEWITHSUNDEEP
powershellcsvactive-directory
bmsthomp
bmsthomp

Reputation: 33

Import-CSV and Foreach to use Get-ADUser

I have a CSV file that looks like this:

name    
[email protected]  
[email protected]  
...

I would like to loop through each email address and query AD for that address to grab the user objects ID. I have been able to do this using a script, but I would like to be able to do it using just one line.

This is what I've done so far:

import-csv -path .\csv_file.csv | foreach-object { get-aduser -filter { proxyaddresses -like "*$_.name*} | select name } | out-file .\results.csv

This obviously doesn't work and I know it has something to do with how I am handling my $_ object in the foreach loop.

I'm hoping for the output to look something like:

[email protected],userID1  
[email protected],userID2
...

Upvotes: 3

Views: 50518

Answers (2)

mjolinor
mjolinor

Reputation: 68341

If you're using Exchange this can be much simpler and faster if you use the Exchange cmdlets:

Import-CSV -Path .\csv_file.csv | ForEach-Object { 
Get-Recipient $_ |
Select PrimarySMTPAddress,SamAccountName 
} | Export-CSV .\results.csv -NoTypeInformation

Exchange requires all email address to be unique, and maintains it's own internal database that uses email address as a primary index so it can return the DN and SamAccountName that goes with that email address almost immediately.

AD doesn't require them to be unique, so it doesn't index on that attribute and it has to search every user object looking for the one that has that email address in it's proxy address collection.

Upvotes: 2

Matt
Matt

Reputation: 46730

You are filtering on the property proxyaddresses however that is not part of the default property set that Get-AdUser returns. Also your code had a errant " which might have been a copy paste error.

Import-CSV -Path .\csv_file.csv | ForEach-Object { 
    Get-ADUser -Filter "ProxyAddresses -like '*$($_.name)*'"  -Properties ProxyAddresses,EmailAddress | select EmailAddress,SamAccountName 
} | Export-CSV .\results.csv -NoTypeInformation

-Filter can be tricky sometimes as it is looking for string input. Wrap the whole thing in quotes and use a sub expression to ensure that the variable $_.Name is expanded properly and has is asterisks surrounding it.

Since you are also looking for emailaddress we add that to the properties list as well. I will assume the second column is for samaccountname.

We also use Export-CSV since that will make for nice CSV output.

Upvotes: 3

Related Questions