Get SamAccountName from list of various names

Question

I have a list of names taken from Oracle that Im trying to find the SamAccountName for. The file is a CSV and some names are "last, first" or "last, first middle initial" and some have three or four names like "alpha bravo charlie delta". The names in the list are likely not the same as listed in AD. Trying to figure out how to sort through this to find AD accounts. The code I currently have is not producing any results.

Import-Module ActiveDirectory
Import-Csv "\\server\users\folder\Oracle_HR.csv" 

ForEach-Object{    
  Get-ADUser -Filter { Name -like "'$($_.name)'"} -Properties Name |
    Select-Object Name,SamAccountName |
      Export-CSV "\\server\users\folder\Oracle_ADs.csv" -NoTypeInformation
}

As an aside: It's best to [avoid the use of script blocks (`{ ... }`) as `-Filter` arguments](https://stackoverflow.com/a/44184818/45375). — mklement0, Nov 29 '18 at 14:14

mklement0 · Accepted Answer · 2018-11-29T16:50:12.567

The answers by Gabriel Luci and Mathias R. Jessen give good advice on "fuzzy" filtering of AD users.^[1]

However, your primary problem is that your ForEach-Object call is not receiving pipeline input, because you haven't connected it to output from the Import-Csv call.

Simply join the two commands with |:

 Import-Csv "\\server\users\folder\Oracle_HR.csv" | ForEach-Object { # ...

Secondarily, your -Filter argument { Name -like "'$($_.name)'"} mistakenly applies two layers of quoting and is missing wildcard characters, given that -like compares against the entire field, yet you want substring matching.

Since it's better to avoid the use of script blocks ({ ... }) as -Filter arguments, use a string:
```
"Name -like `"*$($_.name)*`"" # Note the enclosing '*' to match substrings
```
Note that I've used embedded " quoting (escaped as `") rather than ' quoting, so as not to break the filter with names that contain ', such as O'Malley.
That said, if, as your question suggests, the names in your CSV data aren't direct substrings of the AD users' .Name property values, the above filter will not be enough, and even the ANR (Ambiguous Name Resolution) technique shown in the linked answers may not be enough.

Thirdly, your Export-Csv call is misplaced: because it is inside the ForEach-Object script block, the same output file gets overwritten in every iteration.

Restructure your command as follows:

 Import-Csv ... | ForEach-Object { ... } | Export-Csv ...

Optional reading: ForEach-Object behavior when not providing pipeline input:

The associated script block is executed once.
$_, the automatic variable that contains the current input object, is $null.

[1] Note that the search term in the LDAP filter may need escaping ; per this article, the characters * ( ) \ NUL require escaping and must be escaped as \<hh>, where <hh> is the two-digit hex representation of the char's ASCII code (e.g., * must be escaped as \2A):

$escapedName = -join $(foreach ($c in [char[]] $_.name) { if ('*', '\', '(', ')', "`0" -contains $c) { '\' + ([int] $c).ToString('X2') } else { $c } }) 

Get-ADUser -LDAPFilter "(anr=$escapedName)"

With $_.name containing string "James* (Jimmy) Smith\Smyth`0", $escapedName would evaluate to literal James\2A \28Jimmy\29 Smith\5CSmyth\00

Gabriel Luci · Answer 2 · 2018-11-29T14:20:34.773

Keep in mind that the property names in PowerShell are not named the same as the attributes in AD. The Name property corresponds to both the name and cn attributes in AD (both attributes are always the same).

There is also DisplayName (displayName in AD), GivenName (givenName), and Surname (sn). You could try matching against the DisplayName:

Get-ADUser -Filter "DisplayName -eq '$($_.name)'"

If none of those properties match your data exactly, you will have some trouble. No one thing you do will probably work for every account. Hopefully this is just a one-time job and you can work through them in pieces (try one thing, take out the ones that work, and try something different on the rest).

One thing you can try is using AD's Ambiguous Name Resolution (ANR), which will match a search string against several different attributes and even match a first and last name against givenName and sn. That might work with some in your list. You would use it like this:

Get-ADUser -LDAPFilter "(anr=$($_.name))"

If none of that works, you'll have to split the names (for example, by spaces: $_.name.Split(" ")) and try to match pieces of it to different attributes. You'll have to look at your data and see what works.

I get an error when using that as a filter. 'Get-ADUser : The search filter cannot be recognized' — pystackit, Nov 29 '18 at 14:31

score 3 · Answer 3 · edited Nov 29 '18 at 14:48

One approach is to use the Ambiguous Name Resolution feature in Active Directory.

It'll do fuzzy matching against multiple attributes, like the displayName, Name and mail attributes (among others), so it's pretty good for this exact kind of scenario where you don't necessarily know the order or the names or the full name up front:

Get-ADUser -LDAPFilter "(&(anr=$($_.name)))"

score 0 · Answer 4 · answered Nov 29 '18 at 14:04

I recommend using LDAPFilter and Ambiguous Name Resolution (anr) with Get-ADUser. The algorithm looks up several name fields in different orders to find matches:

Get-ADUser -LDAPFilter "(anr=John Doe)"

Or modifying your code:

Get-ADUser -LDAPFilter "(anr=$($_.name))"

score 0 · Answer 5 · answered Nov 29 '18 at 14:20

You could try something like the following:

Import-Module ActiveDirectory
$allUsers = Get-Content "\\server\users\folder\Oracle_HR.csv"
$users = @()
ForEach($obj in $allUsers){
    $user = Get-ADUser -Filter { GivenName -like $obj} -Properties Name, samAccountName
    if(!$user){
        $user = Get-ADUser -Filter { SurName -like $obj} -Properties Name, samAccountName
    }
    if(!$user){
        $user = Get-ADUser -Filter { Name -like $obj} -Properties Name, samAccountName
    }
    if(!$user){
        Write-Host "User $obj could not be found" -ForegroundColor Red
    }else{
        $users += $user
    }       
}

$users | Select-Object Name,SamAccountName | Export-CSV "\\server\users\folder\Oracle_ADs.csv" -NoTypeInformation

You might need to split the values also like:

Import-Module ActiveDirectory
$allUsers = Get-Content "\\server\users\folder\Oracle_HR.csv"
$users = @()
ForEach($obj in $allUsers){
    $objSplit = $obj.Split(",")
    foreach($split in $objSplit){
        $user = Get-ADUser -Filter { GivenName -like $split} -Properties Name, samAccountName
        if(!$user){
            $user = Get-ADUser -Filter { SurName -like $split} -Properties Name, samAccountName
        }
        if(!$user){
            $user = Get-ADUser -Filter { Name -like $split} -Properties Name, samAccountName
        }
        if(!$user){
            Write-Host "User $split could not be found" -ForegroundColor Red
        }else{
            if($users.samAccountName -notcontains $user.SamAccountName){
                $users += $user
            }
        }
    }      
}

$users | Select-Object Name,SamAccountName | Export-CSV "\\server\users\folder\Oracle_ADs.csv" -NoTypeInformation

Unfortunately neither of those gave any results, but thanks. — pystackit, Nov 29 '18 at 14:31

Get SamAccountName from list of various names

5 Answers5