Solved

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

Posted on 2009-07-16
9
3,787 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
 
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
Problems using Powershell and Active Directory?

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: 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

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

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
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 …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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…

911 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

17 Experts available now in Live!

Get 1:1 Help Now