Solved

Create a CSVReport by comparing a csv1 file against AD

Posted on 2013-05-22
16
215 Views
Last Modified: 2013-05-23
Hello,
I have the following CSV file with the following headers:
Last Name      First Name      Job Title      Clock Number

Clock Number is the primary key

I have to read that file and compare it against AD on this OU: "OU=ou,DC=domain,DC=com" but not reading anything from the VIP ou.

What do i need to compare against AD ? firstname, lastname, title and employeeNumber.

Conditionals:
(1) IF clock number from CSV file MATCHES EmployeeNumber in AD THEN check for firstname, lastname, title AND IF there is any discrepancy create a report.
(2) IF clock number from CSV NOT in AD, add into the same report users that do not have EmployeeNumber

So far, I have:

$File = Import-Csv "C:\scripts\ceridian\05-22-2013_CeridianExport.csv"

$Ad = Get-ADUser -SearchBase "OU=ou,DC=domain,DC=com" -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber | Where { $_.DistinguishedName -notmatch "OU=VIP" } | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeNumber

Open in new window

0
Comment
Question by:namerg
  • 9
  • 6
16 Comments
 
LVL 7

Expert Comment

by:BT15
ID: 39188013
i changed one of your AD properties to be Surname instead of SN.

If this doesnt work right, it might be because your field names have spaces in them :) I almost always mess that up on the first shot.


$File = Import-Csv "C:\scripts\ceridian\05-22-2013_CeridianExport.csv"

$Ad = Get-ADUser -SearchBase "OU=ou,DC=domain,DC=com" -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber | Where { $_.DistinguishedName -notmatch "OU=VIP" } | Select-Object  sAMAccountName,surname,givenName,department,title,telephoneNumber,employeeNumber
$exceptionreport = @()
foreach ($line in $file){

$user = ad | ? {$_.employeenumber -eq $line.("Clock Number")}

if ($user){
    if (($user.givenname -ne $line.("First Name")) -or ($user.surname -ne $line.("Last Name"))){
		$exceptionline = "$user.employeenumber has a name that does not match"
		} 
	} Else {
	$exceptionline = '$line.("Clock Number") does not have an employee ID in AD'
	}
$exceptionreport = @(
$exceptionreport
$exceptionline
)
}
$exceptionreport | sc c:\somefile.txt

Open in new window

0
 

Author Comment

by:namerg
ID: 39188019
What does sc stands for ? Export-csv ?
0
 

Author Comment

by:namerg
ID: 39188026
Got the following error:
You must provide a value expression on the right-hand side of the '-' operator.
At C:\scripts\ceridian\Ceridian_vs_AD_Exceptions_1.ps1:24 char:72
+ $exceptionreport | "c:\scripts\ceridian\2013-05-22_AD_Exceptions.csv" - <<<< NoTypeInformation
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : ExpectedValueExpression
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 7

Expert Comment

by:BT15
ID: 39188395
sc stands for set-content. you can abbreviate it as i did and it will still work (gc is get-content for future reference)

you  are missing an expression. should do that line like this:

$exceptionreport | export-csv "c:\scripts\ceridian\2013-05-22_AD_Exceptions.csv"

except since the output isnt comma delimited (assuming you kept it as provided)

$exceptionreport | sc "c:\scripts\ceridian\2013-05-22_AD_Exceptions.txt"
0
 

Author Comment

by:namerg
ID: 39188421
Getting this error:
The term 'Ad' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path i
At C:\scripts\ceridian\Ceridian_vs_AD_Exceptions_1.ps1:10 char:12
+     $user = Ad <<<<  | ? {$_.employeenumber -eq $line.("Clock Number")}
    + CategoryInfo          : ObjectNotFound: (Ad:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
0
 
LVL 7

Expert Comment

by:BT15
ID: 39188480
Replace it with $AD
0
 

Author Comment

by:namerg
ID: 39188637
same thing but with AD
0
 

Author Comment

by:namerg
ID: 39188850
I think this project is a little bit complicated subsun and footech helped with some few months ago and i am going back again. I am trying to remember the whole logic..this is what I have so far...and as far as i understand the logic it will create a report that mismatches users from CSV against AD, right?

$File = Import-Csv "c:\scripts\ceridian\05-21-2013_CeridianExport.csv"
$Ad = Get-ADUser -SearchBase "OU=ou,DC=domain,DC=com" -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber | Where { $_.DistinguishedName -notmatch "OU=VIP" -and $_.DistinguishedName -notmatch "OU=DA"} | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeNumber
$Result = @()
Foreach ($AD_User in $Ad){
If (!($File | ?{$AD_User.EmployeeNumber -eq $_."Clock Number" -and $AD_User.sn -eq $_."Last Name" -and $AD_User.givenName -eq $_."First Name" -and $AD_User.title -eq $_."title"})){$Result += $AD_User}
}
$Result | Select sn,givenName,title,employeeNumber | Export-Csv "c:\scripts\ceridian\05-22-2013_report.csv" -NoTypeInformation

Open in new window

But in the results, it throws me users that match exactly in the CSV and AD. So, I am confpuzzled.
Thanks for your help
0
 
LVL 7

Accepted Solution

by:
BT15 earned 250 total points
ID: 39188997
now that i am not on my cellphone i think i can be a little more attentive. i my last code snapshot, I had a typo where i referenced a variable without typing a "$" in front of it. I have fixed it below. Does this not work either?:


$File = Import-Csv "C:\scripts\ceridian\05-22-2013_CeridianExport.csv"

$Ad = Get-ADUser -SearchBase "OU=ou,DC=domain,DC=com" -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber | Where { $_.DistinguishedName -notmatch "OU=VIP" } | Select-Object  sAMAccountName,surname,givenName,department,title,telephoneNumber,employeeNumber
$exceptionreport = @()
foreach ($line in $file){

$user = $ad | ? {$_.employeenumber -eq $line.("Clock Number")}

if ($user){
    if (($user.givenname -ne $line.("First Name")) -or ($user.surname -ne $line.("Last Name"))){
		$exceptionline = "$user.employeenumber has a name that does not match"
		} 
	} Else {
	$exceptionline = '$line.("Clock Number") does not have an employee ID in AD'
	}
$exceptionreport = @(
$exceptionreport
$exceptionline
)
}
$exceptionreport | sc c:\somefile.txt 

Open in new window

0
 
LVL 7

Expert Comment

by:BT15
ID: 39189018
the other code you provided does the same thing as mine, though it checks more fields and is a little cleaner at updating its array for results.

if you are getting the same results from from its output that you are from the input, its because something about all of your input values doesnt match AD EXACTLY (maybe a trailing space in your CSV, or something like that)
0
 

Author Comment

by:namerg
ID: 39189034
Wow...i got unexpected results:
Length
213
226
200
$File = Import-Csv "C:\scripts\ceridian\05-22-2013_CeridianExport.csv"

$AD = Get-ADUser -SearchBase "OU=ou,DC=domain,DC=com" -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber | Where { $_.DistinguishedName -notmatch "OU=VIP" -and $_.DistinguishedName -notmatch "OU=DA"} | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeNumber

$exceptionreport = @()
foreach ($line in $file){

$user = $ad | ? {$_.employeenumber -eq $line.("Clock Number")}

if ($user){
    if (($user.givenname -ne $line.("First Name")) -or ($user.surname -ne $line.("Last Name"))){
		$exceptionline = "$user.employeenumber has a name that does not match"
		} 
	} Else {
	$exceptionline = '$line.("Clock Number") does not have an employee ID in AD'
	}
$exceptionreport = @(
$exceptionreport
$exceptionline
)
}
$exceptionreport | export-csv "c:\scripts\ceridian\2013-05-22_AD_Exceptions.csv" -NoTypeInformation

Open in new window

0
 
LVL 39

Assisted Solution

by:footech
footech earned 250 total points
ID: 39189644
You can't use Export-CSV when you're just outputting an array of strings (well, not if you want anything except the Length property), which is what $exceptionreport is.  I'm quite certain that's why BT15 used Set-Content.  My advice, don't make changes to posted code before testing what was posted (unless you really know what the code is doing).  It just increases the amount of places you have to look for errors.

The correct way to put the value of a property into a string like in lines 12 and 15 is as below.
		$exceptionline = "$($user.employeenumber) has a name that does not match"
	$exceptionline = "$($line."Clock Number") does not have an employee ID in AD"

Open in new window


The code in http:#a39188850 appears to work just fine in my testing.
0
 

Author Comment

by:namerg
ID: 39189652
Yeah, I figured it out. I was giving the chance to BT15 to reply otherwise I was going to paste the code tomorrow. And, yes that code was working.. well  i think.... if those fields were exactly the ones that I was going to handle, i did select the right headers and everything came up fine as expected.
0
 

Author Comment

by:namerg
ID: 39189656
By the way, am i going to know who you are in real life ? Same City and State.. :)
0
 
LVL 7

Expert Comment

by:BT15
ID: 39189854
Good morning.

Footech is right on all counts. My $exceptionreport array doesn't contain data that export-csv would know how you handle. Set-content places the data "as is" without attempting to format it.

It seems like you have a working model now?
0
 

Author Comment

by:namerg
ID: 39190911
Yes, BT15. Here it goes:

Import-Module ActiveDirectory
$File = Import-Csv "c:\scripts\ceridian\05-22-2013_CeridianExport.csv"
$AD = Get-ADUser -SearchBase "OU=ou,DC=domain,DC=com" -filter * -Properties sAMAccountName,sn,givenName,title,employeeNumber | Where { $_.DistinguishedName -notmatch "OU=VIP" -and $_.DistinguishedName -notmatch "OU=DA"} | Select-Object  sAMAccountName,sn,givenName,title,employeeNumber
$ADMisMatch = @()
Foreach ($AD_User in $AD){
If (!($File | ?{$AD_User.EmployeeNumber -eq $_."Clock Number" -and $AD_User.sn -eq $_."Last Name" -and $AD_User.givenName -eq $_."First Name" -and $AD_User.title -eq $_."Job Title"}))
    {
        $ADMisMatch += $AD_User
    }
}
$ADMisMatch | Sort-Object -Property "sn", "givenName" | Select sn,givenName,title,employeeNumber | Export-Csv "c:\scripts\ceridian\05-22-2013_ADMisMatch.csv" -NoTypeInformation

Open in new window

0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

803 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