Solved

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

Posted on 2009-07-16
9
3,761 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
Comment Utility

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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:tilbard
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility

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

Chris
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
Set OWA language and time zone in Exchange for individuals, all users or per database.
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

16 Experts available now in Live!

Get 1:1 Help Now