?
Solved

Create a CSVReport by comparing a csv1 file against AD

Posted on 2013-05-22
16
Medium Priority
?
241 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
A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

 
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 1000 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 41

Assisted Solution

by:footech
footech earned 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline

839 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