Solved

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

Posted on 2016-08-08
3
96 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 85

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 8

Author Closing Comment

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

A hard and fast method for reducing Active Directory Administrators members.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

623 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