Solved

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

Posted on 2016-08-08
3
74 Views
Last Modified: 2016-08-08
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.
0
Comment
3 Comments
 
LVL 16

Assisted Solution

by:FOX
FOX earned 100 total points
ID: 41747326
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"
0
 
LVL 83

Accepted Solution

by:
oBdA earned 400 total points
ID: 41747336
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"
1
 
LVL 7

Author Closing Comment

by:Senior IT System Engineer
ID: 41748061
Thanks guys !
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now