3

I have a CSV file that looks like this:

name    
fname1lname1@companyemail.com  
fname2lname2@companyemail.com  
...

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:

fname1lname1@companyemail.com,userID1  
fname2lname2@companyemail.com,userID2
...
bmsthomp
  • 33
  • 1
  • 1
  • 3

2 Answers2

3

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.

Community
  • 1
  • 1
Matt
  • 45,022
  • 8
  • 78
  • 119
  • This worked perfectly. Thanks! I think the filter thing was really throwing me off. I'll have to remember that for the future. – bmsthomp Jan 23 '15 at 17:11
  • I had been trying something similar to this all morning and couldn't get the syntax right for `'*$($_.name)*'"` component .... godsend! +1 – Danijel-James W Apr 09 '18 at 03:02
2

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.

mjolinor
  • 66,130
  • 7
  • 114
  • 135