[PowerShell] Saving Activie Directory Query Output to CSV

[Senario]

HR department asks whether user account information in HR system, especially employee ID, is maching with one in Active Directory. Hundres of user account name(SamAccountName) has been given and employee ID should be returned to HR by filling out employee ID aligned with user account name.

 

[Objectives]

Write Powershell script to 

- getting each line of user account name from csv(comma separated value) file

- executing AD query with user account name to get employeeID

- saving employeeID in the same row of user account name

 

[Processes]

1. Open csv file which has user account name and make sure that user account name has no space after the name value

2. Run a PowerShell script and get each row value of 'SamAccountName' column

PS C:\Users\jaehui.yoon> Import-CSV -Path "C:\PowerShell\New Text Document.csv" | forEach{        
    Write-output $_.SamAccountName
}


Stefan.baier		
Christoph.Grobelny		
Thomas.Imgrund		
Andreas.Rosentalski		
Gerd.Schuepferling		
Moustafa.Sefer
Michael.Stocker		
Erkut.Turan
Enes.Turhan

"Import-CSV" is a command fetching csv file. Please refer to the below link for more information

Import-Csv (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Learn

Import-Csv (Microsoft.PowerShell.Utility) - PowerShell

 

With pipeline(|), data from "Import-CSV" can be manipluate with other commands. In this case, "forEach" command is used to get each row to process AD command, "Get-ADUser". "Get-ADUser" is a command to call data from Active Directory.

Get-ADUser (ActiveDirectory) | Microsoft Learn

Get-ADUser (ActiveDirectory)

 

3. Now, create a custom table to save output from "Get-ADUser" command by creating columns and maching output data

$users | foreach{
    $info = Get-AdUser -Properties SamAccountName, EmployeeID -filter "SamAccountName -eq '$_'"    
    $out=[pscustomobject]@{
    'UserPrincipalName' = $info.UserPrincipalName
    'EmployeeID' = $info.EmployeeID
    'Enabled' = $info.Enabled
    }    
}

 

4. Then save the out by using "Export-CSV" command. For the data comes out line by line due to "forEach" command, output data should be saved with -Append option

$out | export-csv -path C:\PowerShell\test__.csv -Append -NoTypeInformation -Force

Export-Csv (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Learn

Export-Csv (Microsoft.PowerShell.Utility) - PowerShell

5. Open the saved csv file to make sure the output is saved with desired values

 

[PowerShell Code]

#Get AD User employee ID
Get-AdUser -Properties SamaccountName,EmployeeID,userprinciplename,primarygroup,legacyExchangeDN,objectcategory -filter "SamAccountName -eq 'jaehui.yoon'"

#Get user properties
Get-AdUser -Properties * -filter "EmployeeID -eq '102169'"

#Set user Employee ID
Set-ADUser jaehui.yoon -EmployeeID 102169

#Getting data from CSV, sending query to AD and save the result in CSV format
#import CSV file
$csv = import-csv -path "C:\PowerShell\New Text Document.csv"

#get specific data from column
$users = $csv.SamAccountName

#ran query for each data and save as csv format
$users | foreach{
    $info = Get-AdUser -Properties SamAccountName, EmployeeID -filter "SamAccountName -eq '$_'"    
    $out=[pscustomobject]@{
    'UserPrincipalName' = $info.UserPrincipalName
    'EmployeeID' = $info.EmployeeID
    'Enabled' = $info.Enabled
    }

    $out | export-csv -path C:\PowerShell\test__.csv -Append -NoTypeInformation -Force
}