Link to home
Create AccountLog in
Avatar of namerg
namergFlag for United States of America

asked on

Create a CSVReport by comparing a csv1 file against AD

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

Avatar of BT15
BT15
Flag of United States of America image

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

Avatar of namerg

ASKER

What does sc stands for ? Export-csv ?
Avatar of namerg

ASKER

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
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"
Avatar of namerg

ASKER

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
Replace it with $AD
Avatar of namerg

ASKER

same thing but with AD
Avatar of namerg

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of BT15
BT15
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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)
Avatar of namerg

ASKER

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

SOLUTION
Avatar of footech
footech
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of namerg

ASKER

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.
Avatar of namerg

ASKER

By the way, am i going to know who you are in real life ? Same City and State.. :)
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?
Avatar of namerg

ASKER

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