We help IT Professionals succeed at work.

Powershell script to list user status based on CSV file not working due to apostrophe (Irish surname) !

Hi All,

Can anyone here please assist me in modifying the PowerShell script below so that it works for the Irish surname or any surname with the Apostrophe ?

Script:
$names = Import-CSV "C:\test\names.csv"
$outFile = "C:\test\status.csv"
if (Test-Path $outFile) {Remove-Item $outFile -Force}
Add-Content -Path $outFile -Value "name,samaccountname,enabled"

foreach ($name in $names)
{
    $result = Get-ADUser -filter "Surname -eq '$($name.surname)' -and GivenName -eq '$($name.firstname)'" -Properties * | select enabled,samaccountname,displayname
    Add-Content -Path $outFile -Value "$($result.displayname),$($result.samaccountname),$($result.enabled)"
}

Open in new window


The script is comparing the name of the people in the CSV file

CSV File format:
First Name, Last Name
Jason, Smith
April, O'Neill
Franky, O'Hara
Lisa,Auchterlonie-Crooks
...

Open in new window


with the Active Directory users and the result will be exported into another CSV file with Display Name,samAccountName,account status column.

Any help would be greatly appreciated.

Thanks.
Comment
Watch Question

FOXActive Directory/Exchange Engineer
Top Expert 2015
Commented:
The names.csv should contain the AD usernames instead of the first and last names. The header of the username should read Users. From the username we can now pull out whatever we want to do the comparison.

Import-csv "c:\temp\names.csv" | ForEach{Get-ADuser  $_.users -properties * | ft Surname,GivenName,samaccountname,enabled} | out-file "c:\temp\namesreport"
Most Valuable Expert 2019
Most Valuable Expert 2018
Commented:
First, you need to correct your csv file so that the column names actually match the properties you're using in the script.
GivenName, Surname
Jason, Smith
April, O'Neill
Franky, O'Hara
Lisa,Auchterlonie-Crooks

Open in new window

The you can use an ldap filter:
$names = Import-CSV "C:\test\names.csv"
$outFile = "C:\test\status.csv"
if (Test-Path $outFile) {Remove-Item $outFile -Force}
Add-Content -Path $outFile -Value "name,samaccountname,enabled"

foreach ($name in $names) {
    $result = Get-ADUser -LDAPFilter "(&(sn=$($name.Surname))(givenName=$($name.GivenName)))" -Properties * | Select-Object enabled,samaccountname,displayname
    Add-Content -Path $outFile -Value "$($result.displayname),$($result.samaccountname),$($result.enabled)"
}

Open in new window


EDIT: corrected header --> "Surname" instead of "sn"

Author

Commented:
Thanks guys !