Exporting certain users' samAccountName from AD to csv with only EmployeeNumbers known

Question

I am trying to export certain users' SamAccountName and EmployeeNumber from AD to csv. I only have EmployeeNumbers from HR in csv file and have to match it to SamAccountName in AD. My code does not work past 'if' condition. When I echo output there are same values for $a and $b all in String type. This file C:\temp\UsersToDisable.csv contains 4 and 3 number with column name "EmployeeNumber". This is what i came up with:

Import-Module ActiveDirectory
$Nums = Import-Csv "C:\powershell\EmployeeNumbers.csv"
$Users = Get-ADUser -Filter "*" -Property EmployeeNumber -SearchBase 
"DC=my,DC=example,DC=com" | Where { $_.EmployeeNumber -ne $null }  | 
Select SamAccountName,EmployeeNumber
Foreach ($user in $Users)
{
$EmployeeNumber = $user.EmployeeNumber

foreach ($Line in $Nums)
    {   
         $number = $line.EmployeeNumber
         $a = $number.toString() 
         $b = $EmployeeNumber.toString()
        echo $a $b
        if($a -eq $b) 
            {
                echo $user.SamAccountName
                $result += ,(Get-ADUser $user.SamAccountName -Properties * | Select SamAccountName,employeeNumber)
            }
    }
} $result | Export-CSV "C:\temp\CCI_All_Users.csv" -NoTypeInformation

Thank you for any advice!

score 1 · Answer 1 · answered Dec 21 '21 at 19:42

Loading all the users from the directory and then searching once again for the same user in the directory is not an ideal solution for performance reason.

Try the following code. For each number from the input file, search in the directory the user having the current EmployeeNumber, requesting to load the EmployeeNumber property. Then select only the desired properties and export them to CSV.

Import-Module ActiveDirectory
$Nums = Import-Csv "C:\powershell\EmployeeNumbers.csv"
$Nums | ForEach-Object {
    Get-ADUser -LdapFilter "(EmployeeNumber=$_)" -Property EmployeeNumber -SearchBase "DC=my,DC=example,DC=com" |
    Select-Object SamAccountName, EmployeeNumber
} | Export-CSV "C:\temp\CCI_All_Users.csv"

Santiago Squarzon · Answer 2 · 2021-12-21T22:29:34.540

0

An alternative to Hazrelle's helpful answer, very similar but instead of looping over each line of the CSV, taking advantage of the LDAPFilter capabilities with the help of some string manipulation:

# This assumes there is a column named "EmployeeNumber" in the CSV

$Nums = (Import-Csv "C:\powershell\EmployeeNumbers.csv").EmployeeNumber

# Assuming Nums is an Array with Employee Numbers. i.e.:
# $Nums => A123,A456,A789
# $filter would look like this:
# (|(employeenumber=A123)(employeenumber=A456)(employeenumber=A789))

$filter = [string]::Format(
    '(|(employeenumber={0}))',
    ($Nums -join ')(employeenumber=')
)
$params = @{
    Properties = 'EmployeeNumber'
    SearchBase = 'DC=my,DC=example,DC=com'
    LDAPFilter = $filter
}

Get-ADUser @params | Select-Object samAccountName, EmployeeNumber |
Export-CSV "C:\temp\CCI_All_Users.csv" -NoTypeInformation

Worth mentioning, $result += ,(Get... is not recommended.

edited Dec 21 '21 at 22:29

answered Dec 21 '21 at 19:58

Santiago Squarzon

41,465
5
14
37

Thank you for your response but the script returned empty csv file. I have other code that does return something but because employee numbers contain 3 and 4 digit numbers it confuses with other users. that contain those 3 digits in row. `Import-Module ActiveDirectory $Nums = Import-Csv "C:\powershell\EmployeeNumbers.csv" foreach ($Line in $Nums) { $num = $Line.EmployeeNumber get-aduser -filter "EmployeeNumber -like '*$num*'" -properties employeeNumber -SearchBase "DC=my,DC=example,DC=com" | Export-CSV "C:\temp\All_Users.csv" -NoTypeInformation -Append }` – Watcher Dec 21 '21 at 21:09
@Watcher My answer was meant to find exact matches from the `EmployeeNumber` provided on the CSV, your example is using `-like *..*` which would be bringing incorrect information. – Santiago Squarzon Dec 21 '21 at 21:13

Exporting certain users' samAccountName from AD to csv with only EmployeeNumbers known

2 Answers2