Solved

Create a CSVReport by comparing a csv1 file against AD

Posted on 2013-05-22
16
195 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
Comment Utility
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
Comment Utility
What does sc stands for ? Export-csv ?
0
 

Author Comment

by:namerg
Comment Utility
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
 
LVL 7

Expert Comment

by:BT15
Comment Utility
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
Comment Utility
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
Comment Utility
Replace it with $AD
0
 

Author Comment

by:namerg
Comment Utility
same thing but with AD
0
 

Author Comment

by:namerg
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 7

Accepted Solution

by:
BT15 earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
This article will help you understand what HashTables are and how to use them in PowerShell.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

11 Experts available now in Live!

Get 1:1 Help Now