Before we begin, if you are running Windows 7 or above and if you do not have the PowerShell Active Directory module installed, please do so first by downloading and installing “Remote Server Administration Tools”. I personally cannot live without this module.
Please be sure to check out my related blog post which uses the function described in this post:
PowerShell: Add, Remove, Copy And Move Users To Another AD Group Conditionally
Our SQL Server setup
Our SQL Server security setup is Active Directory based. Permissions are setup with AD groups as opposed to individual user names. So, we frequently get requests to add a list of users to a certain group.
For example, let us say we had a database named CrisisManagement which allowed different access to the below AD groups (no names including database names are real)
- CrisisManagement_ReadOnly_Group
- CrisisManagement_Service_Group
- CrisisManagement_Admin_Group
Typical security request
A typical email request would look something like:
Can you please add the below users to “CrisisManagement_ReadOnly_Group”?
- David Smith
- Terry Cole
- James Chapman
- Charles Borges
- Thomas Potter
- Lee Hunziker
- Roger Mireles
- John Folmar
- Glenn Brown
- Barbara Leahy
- Lisa Myers
- Richard West
It is tedious and I am lazy!
Now, the problem is that I have to lookup the AD User account for each of these users to add to the AD group “CrisisManagement_ReadOnly_Group”. Although I still use the AD module command Get-ADUser to lookup names, I have to do so one name at a time like this:
Get-ADUser -Filter ‘(name -like “*David*”) -and (name -like “*Smith*”)’
Lookup + addition to AD group should be automagic!
The above method is tedious and time consuming, especially if it is a long list of users. I would rather paste the list that the sender sent me as is into a PowerShell command and auto-magically add the corresponding accounts to the AD group. If we are unable to find an user, report it as an error.
This function is an attempt at the above goal, which I have already succeeded at partially because I have started using the function and am finding it very handy!
Let me try to show you what I mean. Now that we have the user list from the email, I should be able lookup the AD user names for all the full-names like this:
$userNames = @"
David Smith
Terry Cole
James Chapman
Charles Borges
Thomas Potter
Lee Hunziker
Roger Mireles
John Folmar
Glenn Brown
Barbara Leahy
Lisa Myers
Richard West
"@
#Do the matching with our function and get the results back
$rslt = Get-ADUserNames `
-UserNamesString $userNames `
-Separator "`n"
$rslt | ft
This should give me back a result that looks somewhat like this
Status FullName ADUser ADUserName StatusMessage ------ -------- ------ ---------- ------------- Success David Smith CN=Smith David,OU=Finance,OU=USA,DC=FABRIKAM,DC=COM DAVIDS Match found for criteria: (name -like '*David*') Error Terry Kole No matches found! Success James Chapman CN=Chapman James,OU=Finance,OU=USA,DC=FABRIKAM,DC=COM JAMESC Match found for criteria: (name -like '*James*') Success Charles Borges CN=Borges Charles,OU=Finance,OU=USA,DC=FABRIKAM,DC=COM CHARLESB Match found for criteria: (name -like '*Charles*') Success Thomas Potter CN=Potter Thomas,OU=Finance,OU=USA,DC=FABRIKAM,DC=COM THOMASP Match found for criteria: (name -like '*Thomas*') -and (name -like '*Potter*') ..... ..... ..... .....
Then, I should be able to take those results and just add it to the AD group as shown here:
#If the users need to be added to a group specify the group name here..
$addUsersToADGroup = 'CrisisManagement_ReadOnly_Group'
Write-Host "Current group members of $addUsersToADGroup"
Get-ADGroupMember $addUsersToADGroup | select name | ft
foreach($user in $rslt)
{
if ($user.Status -eq 'Success')
{
Add-ADGroupMember -Identity $addUsersToADGroup -Members $user.ADUserName
}
else
{
Write-Warning "Could not add user $($user.FullName)! Error: $($user.StatusMessage)"
}
}
Write-Host "Current group members of [$addUsersToADGroup] after additions"
Get-ADGroupMember $addUsersToADGroup | select name | ft
The output would then say who the group members were before and after our addition. If there were errors in the lookup (or) no matches, they will show up as warnings.
WordPress has issues with PowerShell block comments, so, I am separating out the comments from the code
Code Comments
#
#######################
#
.SYNOPSIS
Giving a text with a set of names, finds matching AD user ID
.DESCRIPTION
A lot of times, we get email requests listing a bunch of names
requesting access to a specific resource. Copying and pasting the text
as input to this function should hopefully help find the usernames quickly
.INPUTS
User list
.OUTPUTS
ADUserName
.EXAMPLE
#This is a simple list of full names of users to lookup the AD account for
$userNames = @"
David Smith
Terry Cole
James Chapman
Charles Borges
Thomas Potter
Lee Hunziker
Roger Mireles
John Folmar
Glenn Brown
Barbara Leahy
Lisa Myers
Richard West
"@
#Do the matching with our function and get the results back
$rslt = Get-ADUserNames `
-UserNamesString $userNames `
-Separator "`n"
$rslt | ft
#If the users need to be added to a group specify the group name here..
$addUsersToADGroup = 'CrisisManagement_ReadOnly_Group'
Write-Host "Current group members of $addUsersToADGroup"
Get-ADGroupMember $addUsersToADGroup | select name | ft
foreach($user in $rslt)
{
if ($user.Status -eq 'Success')
{
Add-ADGroupMember -Identity $addUsersToADGroup -Members $user.ADUserName
}
else
{
Write-Warning "Could not add user $($user.FullName)! Error: $($user.StatusMessage)"
}
}
Write-Host "Current group members of [$addUsersToADGroup] after additions"
Get-ADGroupMember $addUsersToADGroup | select name | ft
.NOTES
Version History
v1.0 - Jan 11, 2018. Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com]
.LINK
sqljana.wordpress.com
#
#
Actual Code
function Get-ADUserNames
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string] $UserNamesString,
[Parameter(Mandatory=$false)]
[string] $Separator = "`n",
[Parameter(Mandatory=$false)]
[string] $searchBase = '' #Example "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM"
)
[string] $fn = $MyInvocation.MyCommand
[string] $stepName = "Begin [$fn]"
[int] $fullNameCounter = 0
[int] $splitNameCounter = 0
try
{
$stepName = "[$fn]: Split the string on the separator"
#---------------------------------------------------------------
Write-Verbose $stepName
#Split the entire text based on separator
$individuals = $UserNamesString.Split($Separator,[System.StringSplitOptions]::RemoveEmptyEntries)
$stepName = "[$fn]: Lookup each user now"
#---------------------------------------------------------------
Write-Verbose $stepName
foreach ($fullName in $individuals)
{
$splitNameCounter = 0
$fullNameCleansed = ($fullName.Trim() -replace '[\W]', ' ').Trim() #replace all non-alphabets with space!
if ($fullNameCleansed.Length -gt 0)
{
$stepName = "[$fn]: Working on - {0}" -f $fullNameCleansed
#---------------------------------------------------------------
Write-Verbose $stepName
$fullNameCounter ++
Write-Progress -Activity "Overall progress:" `
-PercentComplete ([int](100 * $fullNameCounter / $individuals.Length)) `
-CurrentOperation ("Completed {0}%" -f ([int](100 * $fullNameCounter / $individuals.Length))) `
-Status ("Working on [{0}]" -f $fullNameCleansed) `
-Id 1
$stepName = "[$fn]: Trying complete match on {0}" -f $fullNameCleansed
#---------------------------------------------------------------
Write-Verbose $stepName
#Try to match on the full name first...its fast
if ($searchBase.Trim().Length -gt 0)
{
$matches = @(Get-ADUser -Filter {name -like $fullNameCleansed} -SearchBase $searchBase -Properties '*')
}
else
{
$matches = @(Get-ADUser -Filter {name -like $fullNameCleansed} -Properties '*')
}
$stepName = "[$fn]: Trying complete match on reversed name of {0}" -f $fullNameCleansed
#---------------------------------------------------------------
Write-Verbose $stepName
#If we were unable to match on "Firstname LastName", let us try "Lastname FirstName"
if ($matches.Count -eq 0)
{
$nameSplit = $fullNameCleansed.Split(' ',[System.StringSplitOptions]::RemoveEmptyEntries)
[array]::Reverse($nameSplit)
$fullNameCleansedReversed = ($nameSplit -join ' ').Trim()
$matches = @(Get-ADUser -Filter {name -like $fullNameCleansedReversed} -Properties '*')
}
#Perfect match on full name!
if ($matches.Count -eq 1)
{
New-Object PSObject –Property `
@{FullName = $fullNameCleansed
ADUserName = $matches[0].SAMAccountName
Status = 'Success'
StatusMessage = "Match found for full name"
ADUser = $matches[0]}
}
else
{
$stepName = "[$fn]: Trying piece by piece match on {0}" -f $fullNameCleansed
#---------------------------------------------------------------
Write-Verbose $stepName
#Match the name piece by piece!
$matches = $null
$filterString = ''
#Split the name on spaces (special characters were removed earlier)
$nameSplit = $fullNameCleansed.Split(' ',[System.StringSplitOptions]::RemoveEmptyEntries)
#Loop through the parts of the full name that were split and match with one thing first, two parts next and so on..
foreach($namePart in $nameSplit)
{
$splitNameCounter ++
Write-Progress -Activity "Current name progress:" `
-PercentComplete ([int](100 * $splitNameCounter / $nameSplit.Length)) `
-CurrentOperation ("Completed {0}%" -f ([int](100 * $splitNameCounter / $nameSplit.Length))) `
-Status ("Working on [{0}]" -f $namePart) `
-Id 2 `
-ParentId 1
if ($filterString.Trim().Length -eq 0)
{
#First time for the name...let us try to match on the first name part in full name
$filterString = "(name -like '*$namePart*') "
}
else
{
#We need to restrict the filter by more conditions based on the other name parts
$filterString += " -and (name -like '*$namePart*') "
}
$stepName = "[$fn]: Get the matches for the current filtration - {0}" -f $filterString
#---------------------------------------------------------------
Write-Verbose $stepName
#User SearchBase if provided
if ($searchBase.Trim().Length -gt 0)
{
$matches = @(Get-ADUser -Filter $filterstring -SearchBase $searchBase -Properties '*')
}
else
{
$matches = @(Get-ADUser -Filter $filterstring -Properties '*')
}
if ($matches.Count -eq 0)
{
#If we are at the last name part and we have not found anything, sorry!
if ($splitNameCounter -eq $nameSplit.Length)
{
New-Object PSObject –Property `
@{FullName = $fullNameCleansed
ADUserName = ''
Status = 'Error'
StatusMessage = 'No matches found!'
ADUser = $null}
break
}
else
{
#We have not managed to match on a partial name yet but we might match on other name parts (like last name only)
#Since we did not match on the filtration criteria so far, reset the filter
$filterString = ''
}
}
if ($matches.Count -eq 1)
{
New-Object PSObject –Property `
@{FullName = $fullNameCleansed
ADUserName = $matches[0].SAMAccountName
Status = 'Success'
StatusMessage = "Match found for criteria: $filterString"
ADUser = $matches[0]}
break
}
#If we have multiple matches and we are at the last name part...have to quit!
if (($matches.Count -gt 1) -and ($splitNameCounter -eq $nameSplit.Length))
{
New-Object PSObject –Property `
@{FullName = $fullNameCleansed
ADUserName = ''
Status = 'Error'
StatusMessage = "Multiple matches [$($matches.Count)] found for criteria: $filterString"
ADUser = $null}
break
}
}
}
}
}
}
catch
{
[Exception]$ex = $_.Exception
Throw "Unable to get AD account for users. Error in step: `"{0}]`" `n{1}" -f `
$stepName, $ex.Message
}
finally
{
#Return value if any
}
} <span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" >&#65279;</span>
Parallelizing the workload
Below I am using the PoshRSJob module by Boe Prox to parallelize the workload. Please note that it may be more overhead to parallelize a handful of lookups resulting in slower time than a normal call. This is better suited for very large workloads. Also, the way I am splitting is only for illustration. Typically, you would split the group into multiple pieces (varies on your workload) and use the example below as your guide.
#Does parallel workload using PoshRSJob (https://learn-powershell.net/2015/04/19/latest-updates-to-poshrsjob/)
import-module PoshRSJob
$separator = "`n"
$userNames = @"
David Smith
Terry Cole
James Chapman
Charles Borges
Thomas Potter
Lee Hunziker
Roger Mireles
John Folmar
Glenn Brown
Barbara Leahy
Lisa Myers
Richard West
"@
$list = $userNames.Split($separator)
$jobs = $list | Start-RSJob -ScriptBlock {
. c:\PowerShell\Get-AdUsers.ps1
Get-ADUserNames `
-UserNamesString $_ `
-Separator "`n"
} | Wait-RSJob
#Receive the results
$rslt = $jobs | Receive-RSJob | ft
#Cleanup
Get-RSJob | Remove-RSJob
#Display the results
$rslt
From several minutes to seconds
Now, my life is vastly simplified. Just the thought of not having to manually lookup users one by one makes me happy! The time it takes for me to handle requests like the one in this post is a fraction of what it used to be. If you like this function, you may like my other AD/SQL Server related posts too.
If you a ton of names to lookup, it can be slow and you might want to consider using my suggestions that will be immensely useful on how to parallelize a PowerShell workload
SQL Jana 
4 thoughts on “PowerShell: Lookup Active Directory Accounts Flexibly/Exhaustively For A List Of First/Middle/Last Names”