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

This topic contains 10 replies, has 2 voices, and was last updated by Profile photo of Øyvind Kallstad Øyvind Kallstad 2 years, 1 month ago.

  • Author
    Posts
  • #20090
    Profile photo of Greg Dent
    Greg Dent
    Participant

    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?

  • #20091
    Profile photo of Øyvind Kallstad
    Øyvind Kallstad
    Participant

    You can't use single quotes when you want variable expansion. Replace your '$User.Email' with "$($User.Email)".

  • #20110
    Profile photo of Greg Dent
    Greg Dent
    Participant

    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 🙁

    Any other ideas?

  • #20111
    Profile photo of Øyvind Kallstad
    Øyvind Kallstad
    Participant

    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?

  • #20112
    Profile photo of Greg Dent
    Greg Dent
    Participant

    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]"}
    }
  • #20113
    Profile photo of Greg Dent
    Greg Dent
    Participant

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

  • #20114
    Profile photo of Øyvind Kallstad
    Øyvind Kallstad
    Participant

    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.

  • #20115
    Profile photo of Øyvind Kallstad
    Øyvind Kallstad
    Participant

    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}
    }
    
  • #20118
    Profile photo of Greg Dent
    Greg Dent
    Participant

    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.

  • #20119
    Profile photo of Greg Dent
    Greg Dent
    Participant

    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
    	}
    }
  • #20120
    Profile photo of Øyvind Kallstad
    Øyvind Kallstad
    Participant

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

You must be logged in to reply to this topic.