Solved

Create a CSVReport by comparing a csv1 file against AD

Posted on 2013-05-22
16
207 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
 
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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

18 Experts available now in Live!

Get 1:1 Help Now