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

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

LVL 1
tilbardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris DentPowerShell DeveloperCommented:

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
tilbardAuthor Commented:
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
Chris DentPowerShell DeveloperCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Webinar: Miercom Evaluates Wi-Fi Security

It's not just about Wi-Fi connectivity anymore. A wireless security breach can cost your business large amounts of time, trouble, and expense. Plus, hear first-hand from Miercom how WatchGuard's Wi-Fi security stacks up against the competition in our upcoming webinar!

tilbardAuthor Commented:
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
tilbardAuthor Commented:
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
piloziteCommented:
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
Chris DentPowerShell DeveloperCommented:

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
tilbardAuthor Commented:
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
Chris DentPowerShell DeveloperCommented:

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

Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.