Solved

Powershell script to check if a user exists based on EmployeeID field

Posted on 2009-07-16
9
3,807 Views
Last Modified: 2012-05-07
I have a script (relevant portion below) which pulls new hires from our HR database, then creates the user in AD if the user doesn't already have an account. The only unique piece of information stored in both the HR DB and AD is the EmployeeID field (custom extended schema attribute). The problem I'm running into is that the script consistently doesn't seem to check correctly. For instance, user JSmith has an EmployeedID of 1212 in the HR DB. User JSmith exists in AD, and his EmployeeID field is set to 1212. When the script runs, it processes ~10 users total. the first 5 it correctly matches up the EmployeeID in the HR DB with a user in AD and skips them. When it gets to JSmith, the comparison returns back as false, and it tries to create the user, and subsequently bombs out because the user obviously already exists.

The EmployeeID fields in both AD and the HR DB are defined as strings.

$EmployeeList is an array of arrays

I've debugged it and in fact verified that when it gets to the problem user that $NewEmployee[0] IS the correct EmployeeID, and manually checked the users EmployeeID field in AD and verified that it matches exactly.

The IF ELSE portion that sets $userexists was simply so I could manually check to see if it was comparing correctly. Previously I'd just included the comparison in the main IF ELSE block.

Any idea what might be going on?
foreach ($NewEmployee in $EmployeeList) {
	if (Get-QADUser -ObjectAttributes @{"EmployeeID"=$NewEmployee[0]}){
		$userexists = $true
	}else{
		$userexists = $false
	}
	if ($userexists -eq $false){
		#NewEmployee[0] = Employee ID
		#NewEmployee[1] = First Name
		#NewEmployee[2] = Last Name
		#NewEmployee[3] = Supervisors Name
		#NewEmployee[4] = Program employee works at
		#NewEmployee[5] = Title
		#NewEmployee[6] = Date of Hire
 
                #CODE GOES HERE
      }
}

Open in new window

0
Comment
Question by:tilbard
  • 4
  • 4
9 Comments
 
LVL 70

Expert Comment

by:Chris Dent
ID: 24870800

Can you have it output the users it finds as well as the employeeID?

Chris
foreach ($NewEmployee in $EmployeeList) {
  Write-Host "Checking $($NewEmployee[0])"
  $User = Get-QADUser -ObjectAttributes @{"EmployeeID"=$NewEmployee[0]}
  If (!$User) {
    Write-Host "No user found. Re-running query."
    Get-QADUser -ObjectAttributes @{"EmployeeID"=$NewEmployee[0]}
  }
}

Open in new window

0
 
LVL 1

Author Comment

by:tilbard
ID: 24871187
It appears to not be matching the user up at all. I added a line to print who the user should be matched up to in AD.

"Checking   3940
Should be John Smith
No user found. Re-trying."

Used the below to lookup the user:
Q:\$test = Get-QADUser -Identity jsmith -IncludedProperties "EmployeeID"
Q:\Write-Host $test.employeeid
  3940





0
 
LVL 70

Accepted Solution

by:
Chris Dent earned 500 total points
ID: 24871213

Can you try this one?

I wonder if there's just a bit of white space causing the failure. Trim will remove that from either side of the value.

Chris
foreach ($NewEmployee in $EmployeeList) {
  # Make sure there's no white space
  $EmployeeID = $NewEmployee[0].Trim()
  Write-Host "Checking $EmployeeID"
  $User = Get-QADUser -ObjectAttributes @{"EmployeeID"=$EmployeeID}
  If (!$User) {
    Write-Host "No user found."
  }
}

Open in new window

0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 1

Author Comment

by:tilbard
ID: 24872510
Same result. I also ran a get-member on all the values in question just to make sure they were all the same type. All came back as System.String.
0
 
LVL 1

Author Comment

by:tilbard
ID: 24872634
Here's the whole thing, just in case it has anything to do with how I'm grabbing the data initially. The 6268.mdb is just an MS Access 97 DB.
add-pssnapin quest.activeroles.admanagement
Add-PSSnapin NetCmdlets
cls
 
$Errors = ""
$Success = ""
$date = Get-Date
$NewEmployees = $true
 
# Trap for no data returned by OLEDB query
Trap [System.Reflection.TargetInvocationException] {
	$script:NewEmployees=$false
	continue;
}
 
$EmployeeList = @()
$Employee = @()
$adOpenStatic = 3
$adLockOptimistic = 3
$Range = -6
 
$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset
$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = \\chpaydb\harpers$\client\6268.mdb")
$objRecordset.Open("SELECT [hiredate],[id],[firstname],[lastname],[supervisorname],[user1],[title] FROM einfo WHERE (((EInfo.[hireDate])>=DateAdd(""d""," + $Range + ",Date())))", $objConnection,$adOpenStatic,$adLockOptimistic)
$objRecordset.MoveFirst()
if ($NewEmployees){
	do {
		$Employee = ,(($objRecordset.Fields.Item("ID").Value),($objRecordset.Fields.Item("firstname").Value),($objRecordset.Fields.Item("lastname").Value),($objRecordset.Fields.Item("supervisorname").Value),($objRecordset.Fields.Item("user1").Value),($objRecordset.Fields.Item("title").Value),($objRecordset.Fields.Item("hiredate").Value))
		$EmployeeList += $Employee
		$objRecordset.MoveNext()
	} 
	until ($objRecordset.EOF -eq $True)
 
	$objRecordset.Close()
	$objConnection.Close()
}
 
if ($NewEmployees){
	foreach ($NewEmployee in $EmployeeList) {
		#NewEmployee[0] = Employee ID
		#NewEmployee[1] = First Name
		#NewEmployee[2] = Last Name
		#NewEmployee[3] = Supervisors Name
		#NewEmployee[4] = Program employee works at
		#NewEmployee[5] = Title
		#NewEmployee[6] = Date of Hire
		Write-Host "Checking $($NewEmployee[0])"
		$User = Get-QADUser -ObjectAttributes @{"EmployeeID"=$NewEmployee[0]}
		Write-Host "Should be $($NewEmployee[1]) $($NewEmployee[2])"
		if (!$User){
			#$userexists = $false
			Write-Host "No user found."
			Get-QADUser -ObjectAttributes @{"EmployeeID"=$NewEmployee[0]}
		}else{
			#$userexists = $true
		}
	}
}

Open in new window

0
 
LVL 6

Expert Comment

by:pilozite
ID: 24874555
Did you try to put some tempo (ex: start-sleep -s 1) to see if there isn't a probleme with the speed of the request on the DB ?
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 24876642

If you have the values if your "$NewEmployees" array then it should be happy.

I would have been tempted to create an object instead of a simple array because it makes intermediate reporting much easier. For example...

Chris
add-pssnapin quest.activeroles.admanagement
Add-PSSnapin NetCmdlets
cls
 
$Errors = ""
$Success = ""
$date = Get-Date
$NewEmployees = $true
 
# Trap for no data returned by OLEDB query
Trap [System.Reflection.TargetInvocationException] {
        $script:NewEmployees=$false
        continue;
}
 
$EmployeeList = @()
$Employee = @()
$adOpenStatic = 3
$adLockOptimistic = 3
$Range = -6
 
$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset
$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = \\chpaydb\harpers$\client\6268.mdb")
$objRecordset.Open("SELECT [hiredate],[id],[firstname],[lastname],[supervisorname],[user1],[title] FROM einfo WHERE (((EInfo.[hireDate])>=DateAdd(""d""," + $Range + ",Date())))", $objConnection,$adOpenStatic,$adLockOptimistic)
$objRecordset.MoveFirst()
if ($NewEmployees){
        do {
          $EmployeeList += $RecordSet | Select-Object @{n='ID';e={ $_.Fields.Item("ID").Value }}, `
            @{n='FirstName';e={ $_.Fields.Item("firstname").Value }}, `
            @{n='LastName';e={ $_.Fields.Item("lastname").Value }}, `
            @{n='SupervisorName';e={ $_.Fields.Item("supervisorname").Value }}, `
            @{n='User1';e={ $_.Fields.Item("user1").Value }}, `
            @{n='Title';e={ $_.Fields.Item("title").Value }}, `
            @{n='HireDate';e={ $_.Fields.Item("hiredate").Value }}
 
          $objRecordset.MoveNext()
        } 
        until ($objRecordset.EOF -eq $True)
 
        $objRecordset.Close()
        $objConnection.Close()
}
 
# Export the intermediate data to CSV for verification
$EmployeeList | Export-CSV "EmployeeList.csv"
 
If ($NewEmployees){
  $NewEmployees | %{
    # Write back the information about this user. Quoting the ID to see if it is the reason for search failure (White Space).
    Write-Host "Checking for `"$($_.ID)`" ($($_.FirstName) $($_.LastName))"
 
    $User = Get-QADUser -ObjectAttributes @{EmployeeID=$_.ID}
    If (!$User) { Write-Host "Failed Search" }
  }
}

Open in new window

0
 
LVL 1

Author Comment

by:tilbard
ID: 24879466
Ha! You were on the right track with the Trim, just in the wrong location. As it turns out, the value as entered into AD had a leading space, but the app I was using to display it was in fact trimming the space out, so it looked normal to me. The PC I'm on now doesn't have that app, so I had to go directly into ADUC attribute editor, where I noticed it almost immediately.

Thanks for the help!
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 24879480

Well that one gets a "d'oh!". Glad you got to the bottom of it :)

Chris
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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 …
A procedure for exporting installed hotfix details of remote computers using powershell
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

808 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