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

PowerShell Help
1

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 'email@domain.com'}

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?

2

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

3

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?

4

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?

5

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]"}
}
6

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

7

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 'email@domain.com'}

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 'email@domain.com'}

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.

8

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}
}
9

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.

10

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
	}
}
11

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