Simple AD script to get SAM account ID's from email address

Hi guys,

This one has me stumped. I’ve written some complicated scripts in my time using Powershell, some of which I am very proud of.

I thought this one would be simple. Alas not, or I am missing something obvious!

I need to modify the UPN on a bunch of people’s AD user accounts. Unfortunately the user accounts have been provided to me via a list of email addresses. No biggie I thought, I can just write a simple script to get user accounts based on the email address attribute (which is present on all our users) and export that to a new CSV file, and go from there.

I used the following command to obtain the AD account info for a single user:

Get-ADUser -Filter {mail -eq ''}

This works fine; returns a user account purely on the email address using the filter. I’m now applying this command to an imported CSV thus:

Import-Csv csvfile.csv | foreach-object {get-aduser -filter {mail -eq '$_.Email'}}

The column containing emails is named ‘Email’ by the way.

This returns a blank line. No error, just nothing.
Some issue with the syntax maybe I thought. I tried this using a .ps1 file next:

$Users = Import-csv csvfile.csv
foreach [$User in $Users] {
get-aduser -filter {mail -eq '$User.Email'}

Same result.
I’ve tried various other iterations, as well as attempting using specific properties (e.g. -properties *) of the cmdlet with no success.

Any ideas?

You can’t use single quotes when you want variable expansion. Replace your ‘$User.Email’ with “$($User.Email)”.

Thanks for replying.

Same thing happens I’m afraid. I’d already tried using double quotations and bracketing the variable (forgot to mention that).

Hence why I’m properly stuck here :frowning:

Any other ideas?

So the following code does not work for you at all?

Import-Csv csvfile.csv | foreach-object {get-aduser -filter {mail -eq "$($_.Email)"}}

If not, can you post the code that you are currently testing/working on?

No, it just returns me to the PS prompt without any errors or data.

Very weird.

Equally, this does the exact same thing:

$Users = Import-Csv "csvfile.csv"
foreach [$User in $Users] {
get-aduser -filter {mail -eq "$[$User.Email]"}

PS. for some reason the code tags change my brackets out for square ones - IDK why lol.

Ok. We can do this Greg! Let’s start by doing a set of tests.

You said that the following code works as expected, right?

Get-ADUser -Filter {mail -eq ''}

Now, we need to check if we can read the email from the CSV correctly. So pick an email (preferably the same as you used in the above test)

Import-CSV csvfile.csv | Where-Object {$_.Email -eq ''}

This should find the entry with the email you selected. If this test works as well, we move on.

Import-CSV csvfile.csv | Foreach-Object {[array]$adUsers += (Get-ADUser -Filter {mail -eq "$($_Email)"})}

Now you should have a collection of user objects in $adUsers.

Ok, did some testing on my end here. Be sure that you are importing your csv correctly first, by doing this command alone:

Import-CSV csvfile.csv

When I tested, it didn’t work because the delimiter was wrong, so I had to use the -Delimiter parameter. When you are certain that you are reading the CSV right, try this:

$csv = Import-CSV csvfile.csv
foreach($user in $csv){
    $userMail = $user.mail
    Get-ADUser -Filter {mail -eq $userMail}

We have success!

Thank you very much!

So just a case of expanding the variable correctly so that Powershell recognises it… the only real change there was to build out the $userMail variable first.

Noted for next time - this will come in handy as I’ve just had a load more stuff given to me, this time on firstname, surname. Fun!!

Thanks again.

FYI, I was able to export this to a CSV using the following:

$csv = Import-CSV crmusers.csv
foreach[$user in $csv]{
    $userMail = $user.Email
    $userSAM = Get-ADUser -Filter {mail -eq $userMail} -Properties SamAccountName
	foreach [$userID in $userSAM] {
		add-content -Path "crmsamid.csv" -Value $userID.SamAccountName

Cool. Glad to be of help. And continue having fun with PowerShell! :slight_smile: