Solved

How to export into CSV after comparing info between CSV and AD users info ?

Posted on 2013-01-22
19
332 Views
Last Modified: 2013-01-25
Hello,
I would like to create a csv with the following values:
Last Name,First Name,Dept Descrip,Job Title,Clock Number

The created CSV is the resultant of a comparison of a File.csv against AD.

Here is the code
$File = Import-Csv "c:\file.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber  -SearchBase "OU=OU,DC=domain,DC=com" | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
Foreach ($File_User in $FILE){
	$i = 0
	[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
	If ($Search){
		:userloop Foreach ($AD_User in $Search) {
			$i++
			Clear-Host
			write-output ""
			write-host " %%%%%%%%%%%% FOUND THE FOLLOWING EMPLOYEE IN  FILE %%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
			write-host " LASTNAME: $($File_User."Last Name")" -BackgroundColor green -ForegroundColor black
			write-host " FIRST NAME: $($File_User."First Name")" -BackgroundColor red -ForegroundColor black
			write-host " DEPARTMENT: $($File_User."Dept Descrip")" -BackgroundColor green -ForegroundColor black
			write-host " TITLE: $($File_User."Job title")" -BackgroundColor green -ForegroundColor black
			#write-output " TELEPHONE NUMBER: $($File_User.telephoneNumber)"
			#write-output " FLEX ID: $($File_User.employeeID)"
			write-host " CLOCK NUMBER: $($File_User."Clock Number")" -BackgroundColor red -ForegroundColor black
			write-host " %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
			write-output ""
			write-host " &&&&&&&&&&& FOUND THE FOLLOWING EMPLOYEE IN ACTIVE DIRECTORY &&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
			write-host " LASTNAME: $($AD_User.sn)" -BackgroundColor cyan -ForegroundColor black
			write-host " FIRST NAME: $($AD_User.givenName)" -BackgroundColor red -ForegroundColor black
			write-host " DEPARTMENT: $($AD_User.department)" -BackgroundColor cyan -ForegroundColor black
			write-host " TITLE: $($AD_User.title)" -BackgroundColor cyan -ForegroundColor black
			#write-output " TELEPHONE NUMBER: $($AD_User.telephoneNumber)"
			#write-output " FLEX ID: $($AD_User.employeeID)"
			write-host " CLOCK NUMBER: $($AD_User.employeeNumber)" -BackgroundColor red -ForegroundColor black
			write-host " &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
			write-output ""
			Edit_AD_User			
		}
	}
#SO RIGHT HERE IT NEEDS TO CREATE THE CSV FILE FOR THE
#AD USERS THAT DID NOT PASS THE CONDITIONAL IF ON 
#If ($Search) AS MENTIONED ABOVE
	Else {" Lets create a file. No EMPLOYEE Found in ACTIVE DIRECTORY for $($File_User.SN)"}
}

Open in new window

Thanks for your help,
0
Comment
Question by:namerg
  • 10
  • 9
19 Comments
 
LVL 40

Expert Comment

by:Subsun
ID: 38807961
Try saving the discarded objects in a array and export it in the end..
$Result = @()
$File = Import-Csv "c:\file.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber  -SearchBase "OU=OU,DC=domain,DC=com" | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
Foreach ($File_User in $FILE){
	$i = 0
	[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
	If ($Search){
                 <Code>
		}
	}
#SO RIGHT HERE IT NEEDS TO CREATE THE CSV FILE FOR THE
#AD USERS THAT DID NOT PASS THE CONDITIONAL IF ON 
#If ($Search) AS MENTIONED ABOVE
	Else {$Result += $File_User}
}
$Result | Select * | Export-csv c:\Result.csv

Open in new window

0
 

Author Comment

by:namerg
ID: 38810427
@subsun,
I think your code exports users from $File_User and not $Ad

Thanks,
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38810634
You are looking to export the users which are not in AD but CSV right?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:namerg
ID: 38810724
The logic is this:

For example:
File has lastname, firstname
AD has lastname, firstname

So,IF (file.lastname and file.firstname) equals (AD.lastname and AD.firstname) THEN
     "Bingo"
   ELSE
      "Create a csv file with AD.lastname and AD.firstname

Thanks for your help,
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38811329
In that case try...
$Result = @()
$File = Import-Csv "c:\file.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber  -SearchBase "OU=OU,DC=domain,DC=com" | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
Foreach ($File_User in $FILE){
	$i = 0
	[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
	If ($Search){
		:userloop Foreach ($AD_User in $Search) {
		If ($File_User."Last Name" -ne $AD_User.sn -or $File_User."First Name" -ne $AD_User.givenName){$Result += $AD_User}
		<Code>
		}
	}
#SO RIGHT HERE IT NEEDS TO CREATE THE CSV FILE FOR THE
#AD USERS THAT DID NOT PASS THE CONDITIONAL IF ON 
#If ($Search) AS MENTIONED ABOVE
}
$Result | Select * | Export-csv c:\Result.csv

Open in new window

0
 

Author Comment

by:namerg
ID: 38811538
@Subsun,
On line 6, if that statement is not true it will not continue to the next line, in other words.

If file.lastname equals ad.lastname {
  "TRUE, YOU BOTH ARE EQUAL
ELSE
   "YOU ARE NOT EQUAL, LET's CREATE a CSV FILE with the AD. LASTNAME.

Thanks,
PS: By the way, do not take the uppercase offensive or personal.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38811620
Well.. I didn't notice that..

So,IF (file.lastname and file.firstname) equals (AD.lastname and AD.firstname) THEN
     "Bingo"
   ELSE
      "Create a csv file with AD.lastname and AD.firstname
If I am not mistaken above condition result will include all users from AD

Or are you looking for the users which match Last name but no first name and First Name but not last name?

For  example, we have following users in AD and you are going to Edit John cash and you want the other three member (Matching either First Name or Last name) Except Sub Sun added to CSV file. Am I correct?

John cash
john cusack
John Cena
Nick cash
Sub Sun

Sorry, I am confused and sleepy.. :-)
0
 

Author Comment

by:namerg
ID: 38812081
Jajaja, you make me laugh.
Yes, eventually, I will the list of all AD users that does not matc first name, lastname, title,etc,etc, and the CSV will be given to HR, ohh it will be nice if we dump the samaccountname into CSV also.

So, get some rest so you are not confused or sleepy.

Are you from the Ganesh country ? :)
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38814615
Try this.. This will give you the list of users which are there in AD but not in CSV..
$Result = @()
$File = Import-Csv "c:\file.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber  -SearchBase "OU=OU,DC=domain,DC=com" | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
Foreach ($AD_User in $Ad){
If (!($File | ?{$AD_User."Last Name" -eq $_.sn -and $AD_User."First Name" -eq $_.givenName})){$Result += $AD_User}
}
$Result | Select * | Export-Csv C:\report.csv -NoTypeInformation

Open in new window


Ganesh country ???
0
 

Author Comment

by:namerg
ID: 38814663
Hmmm,
Do i need to modify the existing code?
This is what I have and want to keep the existing functionality while I add yours.
$File = Import-Csv "c:\scripts\ceridian\upiemps_Test.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber  -SearchBase "OU=OU,DC=COMPANY,DC=com" | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
$Result = @()
Foreach ($File_User in $FILE){
	$i = 0
	[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
	If ($Search){
		:userloop Foreach ($AD_User in $Search) {
			$i++
			Clear-Host
			write-output ""
			write-host " %%%%%%%%%%%% FOUND THE FOLLOWING EMPLOYEE IN  FILE %%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
			write-host " LASTNAME: $($File_User."Last Name")" -BackgroundColor green -ForegroundColor black
			write-host " FIRST NAME: $($File_User."First Name")" -BackgroundColor red -ForegroundColor black
			write-host " DEPARTMENT: $($File_User."Dept Descrip")" -BackgroundColor green -ForegroundColor black
			write-host " TITLE: $($File_User."Job title")" -BackgroundColor green -ForegroundColor black
			#write-output " TELEPHONE NUMBER: $($File_User.telephoneNumber)"
			#write-output " FLEX ID: $($File_User.employeeID)"
			write-host " CLOCK NUMBER: $($File_User."Clock Number")" -BackgroundColor red -ForegroundColor black
			write-host " %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
			write-output ""
			write-host " &&&&&&&&&&& FOUND THE FOLLOWING EMPLOYEE IN ACTIVE DIRECTORY &&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
			write-host " LASTNAME: $($AD_User.sn)" -BackgroundColor cyan -ForegroundColor black
			write-host " FIRST NAME: $($AD_User.givenName)" -BackgroundColor red -ForegroundColor black
			write-host " DEPARTMENT: $($AD_User.department)" -BackgroundColor cyan -ForegroundColor black
			write-host " TITLE: $($AD_User.title)" -BackgroundColor cyan -ForegroundColor black
			#write-output " TELEPHONE NUMBER: $($AD_User.telephoneNumber)"
			#write-output " FLEX ID: $($AD_User.employeeID)"
			write-host " CLOCK NUMBER: $($AD_User.employeeNumber)" -BackgroundColor red -ForegroundColor black
			write-host " &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
			write-output ""
			Edit_AD_User			
		}
	}
	Else {" Lets create a file. No EMPLOYEE Found in ACTIVE DIRECTORY for $($File_User.SN)"}
}

Open in new window

Where do i put your code?
Ganesh Country =  India
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38814684
You can put it anywhere it's interdependent.. Yes I am from India.. Where are you from?

$File = Import-Csv "c:\scripts\ceridian\upiemps_Test.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber  -SearchBase "OU=OU,DC=COMPANY,DC=com" | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
$Result = @()
Foreach ($AD_User in $Ad){
If (!($File | ?{$AD_User."Last Name" -eq $_.sn -and $AD_User."First Name" -eq $_.givenName})){$Result += $AD_User}
}
$Result | Select * | Export-Csv C:\report.csv -NoTypeInformation
Foreach ($File_User in $FILE){
	$i = 0
	[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
	If ($Search){
		:userloop Foreach ($AD_User in $Search) {
			$i++
			Clear-Host
			write-output ""
			write-host " %%%%%%%%%%%% FOUND THE FOLLOWING EMPLOYEE IN  FILE %%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
			write-host " LASTNAME: $($File_User."Last Name")" -BackgroundColor green -ForegroundColor black
			write-host " FIRST NAME: $($File_User."First Name")" -BackgroundColor red -ForegroundColor black
			write-host " DEPARTMENT: $($File_User."Dept Descrip")" -BackgroundColor green -ForegroundColor black
			write-host " TITLE: $($File_User."Job title")" -BackgroundColor green -ForegroundColor black
			#write-output " TELEPHONE NUMBER: $($File_User.telephoneNumber)"
			#write-output " FLEX ID: $($File_User.employeeID)"
			write-host " CLOCK NUMBER: $($File_User."Clock Number")" -BackgroundColor red -ForegroundColor black
			write-host " %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
			write-output ""
			write-host " &&&&&&&&&&& FOUND THE FOLLOWING EMPLOYEE IN ACTIVE DIRECTORY &&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
			write-host " LASTNAME: $($AD_User.sn)" -BackgroundColor cyan -ForegroundColor black
			write-host " FIRST NAME: $($AD_User.givenName)" -BackgroundColor red -ForegroundColor black
			write-host " DEPARTMENT: $($AD_User.department)" -BackgroundColor cyan -ForegroundColor black
			write-host " TITLE: $($AD_User.title)" -BackgroundColor cyan -ForegroundColor black
			#write-output " TELEPHONE NUMBER: $($AD_User.telephoneNumber)"
			#write-output " FLEX ID: $($AD_User.employeeID)"
			write-host " CLOCK NUMBER: $($AD_User.employeeNumber)" -BackgroundColor red -ForegroundColor black
			write-host " &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
			write-output ""
			Edit_AD_User			
		}
	}
	Else {" Lets create a file. No EMPLOYEE Found in ACTIVE DIRECTORY for $($File_User.SN)"}
}

Open in new window

0
 

Author Comment

by:namerg
ID: 38814892
I'm from Colombia.
I think the code is in a loop.

It created the file with no contents, but still spitting "Let's create a file"
$File = Import-Csv "c:\scripts\file.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber  -SearchBase "OU=OU,DC=COMPANY,DC=com" | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
$Result = @()
Foreach ($File_User in $FILE){
	$i = 0
	[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
	If ($Search){
		:userloop Foreach ($AD_User in $Search) {
			$i++
			Clear-Host
			write-output ""
			write-host " %%%%%%%%%%%% FOUND THE FOLLOWING EMPLOYEE IN FILE %%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
			write-host " LASTNAME: $($File_User."Last Name")" -BackgroundColor green -ForegroundColor black
			write-host " FIRST NAME: $($File_User."First Name")" -BackgroundColor red -ForegroundColor black
			write-host " DEPARTMENT: $($File_User."Dept Descrip")" -BackgroundColor green -ForegroundColor black
			write-host " TITLE: $($File_User."Job title")" -BackgroundColor green -ForegroundColor black
			#write-output " TELEPHONE NUMBER: $($File_User.telephoneNumber)"
			#write-output " FLEX ID: $($File_User.employeeID)"
			write-host " CLOCK NUMBER: $($File_User."Clock Number")" -BackgroundColor red -ForegroundColor black
			write-host " %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
			write-output ""
			write-host " &&&&&&&&&&& FOUND THE FOLLOWING EMPLOYEE IN ACTIVE DIRECTORY &&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
			write-host " LASTNAME: $($AD_User.sn)" -BackgroundColor cyan -ForegroundColor black
			write-host " FIRST NAME: $($AD_User.givenName)" -BackgroundColor red -ForegroundColor black
			write-host " DEPARTMENT: $($AD_User.department)" -BackgroundColor cyan -ForegroundColor black
			write-host " TITLE: $($AD_User.title)" -BackgroundColor cyan -ForegroundColor black
			#write-output " TELEPHONE NUMBER: $($AD_User.telephoneNumber)"
			#write-output " FLEX ID: $($AD_User.employeeID)"
			write-host " CLOCK NUMBER: $($AD_User.employeeNumber)" -BackgroundColor red -ForegroundColor black
			write-host " &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
			write-output ""
			Edit_AD_User			
		}
	}
	Else {
		Write-output " Lets create a file. No EMPLOYEE Found in ACTIVE DIRECTORY for $($File_User.SN)"
		Foreach ($AD_User in $Ad){
				If (!($File | ?{$AD_User."Last Name" -eq $_.sn -and $AD_User."First Name" -eq $_.givenName })) {
					$Result += $AD_User
				}
		}
		$Result | Select * | Export-Csv c:\scripts\ceridian\report.csv -NoTypeInformation
	}
}

Open in new window

0
 
LVL 40

Expert Comment

by:Subsun
ID: 38814913
Don't put it inside the loop..  Did you try my code?
0
 

Author Comment

by:namerg
ID: 38814951
Yes, your code is in there...So, where will i put it.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38815289
I mean what if you run this code #a38814684.. Are you getting the result?
0
 

Author Comment

by:namerg
ID: 38816503
Nada..the report.csv is blank.
Why are you putting -eq and not -ne ?
0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 38816537
Hmm.. we had a mixup for last script..
Does the csv header like.. "First Name" "Last Name"?
Try this and see if you get what you want...
$File = Import-Csv "c:\scripts\ceridian\upiemps_Test.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber  -SearchBase "OU=OU,DC=COMPANY,DC=com" | Select-Object  sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
$Result = @()
Foreach ($AD_User in $Ad){
If (!($File | ?{$AD_User.sn -eq $_."Last Name" -and $AD_User.givenName -eq $_."First Name"})){$Result += $AD_User}
}
$Result | Select * | Export-Csv C:\report.csv -NoTypeInformation

Open in new window

Why are you putting -eq and not -ne ?
I have put a  ! (not) in front of the filter
0
 

Author Comment

by:namerg
ID: 38816590
This is what I have:
$Result = @()
Foreach ($AD_User in $Ad){
If (!($File | ?{$AD_User."Last Name" -eq $_.sn -and $AD_User."First Name" -eq $_.givenName})){$Result += $AD_User}
}
$Result | Select * | Export-Csv c:\scripts\ceridian\report.csv -NoTypeInformation

Open in new window

Nada, report is blank.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38816601
Try the code from #a38816537
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

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

785 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