CODEWITHSUNDEEP
powershellcsvactive-directory
nachbeben
nachbeben

Reputation: 3

PowerShell CSV Import AD Query Username from Mailaddress

I have a CSV file with mail addresses and various other stuff in it and I try to get the AD accounts from it.

CSV looks like this:

TestUserMail;AccountNumber;TestDisplayName
[email protected];1;
[email protected];2;
[email protected];3;

Code looks like this:

Import-Csv -Path 'C:\temp\test.csv' -delimiter ";" | ForEach-Object {
    $_.TestDisplayName = Get-ADUser -Filter {mail -eq '$_.TestUserName'} |
                         Select-Object -ExpandProperty Name
    $_
} | Format-Table

The issue is that somehow it seems that it cannot do the ad query with the $_.TestUserName variable. The result is always that it is empty. If I put a mailadress directly into the script it works just fine.

Does anyone have a idea whats going wrong of any other way how to pull this?

Upvotes: 0

Views: 136

Answers (3)

Sergius_the_Black
Sergius_the_Black

Reputation: 28

First of all, you have a mistake in a filter. There should be an $_.TestUserMail instead of TestUserName. Second, your expression in a single quotes is not expanded. this works.

 Import-Csv -Path 'C:\tmp\test.csv' -delimiter ";" | ForEach-Object {
    $_.TestDisplayName = get-aduser -filter "mail -eq '$($_.TestUserMail)'" | Select-Object -ExpandProperty Name
 $_ } | Format-Table

Take a look at https://learn.microsoft.com/en-us/powershell/module/addsadministration/get-aduser?view=win10-ps

Upvotes: 0

Joost
Joost

Reputation: 1216

The issue is situated in your variable expansion. 

mail -eq '$_.TestUserName'

has 3 issues:

  • you use single quotes, so there is no variable expansion done at all
  • $_ is expanded, and not $_.TestUserName. You need to enclose them in $()
  • the column name is TestUserMail

The correct syntax would be:

mail -eq "$($_.TestUserMail)"

Upvotes: 1

Theo
Theo

Reputation: 61218

You are using the $_ automatic variable wrong. Inside the loop, $_ is one object taken from the CSV file with a property 'TestUserMail'

Something like this should work

Import-Csv -Path 'C:\temp\test.csv' -Delimiter ";" | ForEach-Object {
    $user = Get-ADUser -Filter "mail -eq '$_.TestUserMail'" -Properties mail, Name -ErrorAction SilentlyContinue
    # if we found a user with that email address, output the users Name as object
    # so Format-Table will provide a header.
    if ($user) { $user | Select-Object Name }
}

Upvotes: 1

Related Questions