asked on

Update AD users from HR Data using EmployeeID (VBScript or Powershell)

Guys,
I have attached a sample of our HR data with the users EmployeeID and their Superior (Manager). I am tryoing to update our AD from the information stored in the HR system, but dont kow what is the best and easiest method. Not used Powershell, buthappy to give it a shot.
Is there a way of querying the AD for each user with an EmployeeID, and then Update that employee's information (Title, Location, Manager) The problem I can see is that the employee's manager is also reference by their EmployeeID.
I dont have a problem with extracting a seperate spreadsheet as a lookup, but if not needed would be good. Idea behind this is to be able to run it every few months with the update from HR to keep the AD in order.
Lastly I would like an audit log created with the changes done per user.
I have been told Powershell will let you do it in a few lines, instead of scripting could be pages.
I am not fussed which way, just a reliable method of a regular update.
If there is an article with similar to follow, let me know, but a number of searches have ended up with different methods.
Hope this all make sense, and thanks in advance.
cheers

employee.xls

KenMcF

YOu could use powershell and quest AD cmdlets
The manager has to be a DN.

$users = import-csv c:\users.csv
foreach ($user in $users){
get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=person)(employeeid=$($user.employee))" | set-qaduser -location $user.location -title $user.title -manager (get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=person)(employeeid=$($user.supemp))" ).dn
}

$users = import-csv c:\users.csv
foreach ($user in $users){
get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=person)(employeeid=$($user.employee))" | set-qaduser -location $user.location -title $user.title -manager (get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=person)(employeeid=$($user.supemp))" ).dn
}

Open in new window

Snappa2000

ASKER

You are truely a Guru!

What do I need to add to the line to dump the update per user with a comment of Updated, or NotFound. As there may be occassions that the HR list with the correct employeeid's have not made it to the AD list.

Cheers

KenMcF

You can add an IF statement like in the attached code
This has not been tested so there may be some syntax errors.

$users = import-csv c:\users.csv  
foreach ($user in $users){ 
If ($user -ne $null){
get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=person)(employeeid=$($user.employee))" | set-qaduser -location $user.location -title $user.title -manager (get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=person)(employeeid=$($user.supemp))" ).dn  
} 
}
Else{Write-Host "User $user not found}
}

Open in new window

Snappa2000

ASKER

I am having trouble trying to install the ActiveRoles from Quest on a x64 Win7 PC. It keeps complaining that it needs MS Core XML Services (MSXML) 6.0. Do you know how to get around this? Google hasn't come up with much.

Vishal Patel

Instead you can use Active Directory management tool from Quest or AD manager Plus from Manage Engine.

KenMcF

Can you try to download this and install on your system

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en

Snappa2000

ASKER

KenMcF,
That what was downloaded and is installed happily.. but yet the Quest install cant see it! Ill look into the Manage Engine on now

Snappa2000

ASKER

vipatel,

This is a seperate paid application which will not do what i am after, I am looking at exactly what KenMcF has proposed, so I need it scripted.

Alternatively, a VBS script might also provide an answer, but I do like the simplicity of the powershell answer!

KenMcF

Do you have the RSAT tools installed on the Win7 computer, do you have powershell 2.0 installed. Are you running setup as administartor, right click select run as administrator.

Snappa2000

ASKER

RSAT is installed, Powershell 2.0 is pre-installed on Windows 7, and this has been verified. Setup is an MSI and it elavates the permissions, no option to run as admistrator. I am full administrator on the machine.

I have also verified the install on another machine, with the same results ??????

KenMcF

I tried to replicate in my lab but was not able to. I installed a fresh install of Win7x64. Joined to my test domain, installed RSAT, and then Quest ADcmdlets with no issues. Still looking for a solution.

Snappa2000

ASKER

Stop looking..

I have found the problem!! It was a restricted Download forlder that i had the installs in, with myself being the only user having rights to it.. Maybe being a little to anal about who can access it??

I have revert it back to Administrators of the box and it all works!!

I am just testing the script now

Snappa2000

ASKER

I am having a slight problem.. I have got the first part of the script working using..

Get-QADUser -objectAttributes @{employeeID=$user.employee}

the problem i have is piping it to the the other line..I modified it to resemble the above query..

set-qaduser -location $name.location -title $name.title -manager (Get-QADUser -objectAttributes @{employeeID=$user.supemp}).dn

But it doesnt run.. comes up with 'An empty pipe element is not allowed'

A side note: the HR employee ID's are in the formate 1000XXXX, and the AD is in the format of 100000XXXX (they have added two zeros) and they are using this in a custom program, so cant update AD. Is there a simple line to add 00 from the employeeid and supemp? Was thinking of reading both values in the foreach and modifying them before passing.

I can modify the extract in this instance, so not that critical at the moment

KenMcF

Try this one

I would use the LDAP filter instead of -objectattribute

$users = import-csv c:\users.csv    
foreach ($user in $users){   
	$u = get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=person)(employeeid=$($user.employee)00)"
	If ($u -ne $null){
		set-qaduser -identity $u -location $user.location -title $user.title -manager (get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=person)(employeeid=$($user.supemp)00)" ).dn 
	}   
}  
Else{Write-Host "User $user not found}

Open in new window

Snappa2000

ASKER

getting the below when steping through it:

Get-QADUser : The search filter is invalid.
At D:\Documents\Desktop\EmployeeId.ps1:3 char:18
+ $u = get-qaduser <<<< -LDAPFilter "(&(objectcategory=person)(objectclass=person)(employeeid=
$($user.employee)00)"
+ CategoryInfo : NotSpecified: (:) [Get-QADUser], LdapException
+ FullyQualifiedErrorId : System.DirectoryServices.Protocols.LdapException,Quest.ActiveRoles.A
rsPowerShellSnapIn.Powershell.Cmdlets.GetUserCmdlet

ASKER CERTIFIED SOLUTION

KenMcF

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Snappa2000

ASKER

KenMcF,

Thank you for all your time and efforts, I have finally got to a stage where it is working and would like you to run a sanity check over the code. I couldn't get LDAPFilter working so have reverted back. I have included the code.

I have one question, It runs slow, and was wondering if I can only retrieve the necessary fields of the user instead of everything! (if that's what is slowing things down)

P.S. I renamed the field supemp to manager in the code and the users.csv

clear-host 

$users = import-csv c:\users.csv    

foreach ($user in $users){   
    $eid = $user.employee
	$eid = "100$($eid.substring($eid.Length-7,7))"
    $mid = $user.manager
	$mid = "100$($mid.substring($mid.Length-7,7))"
	# Write-Host "User $eid"
	# Write-Host "manager $mid"

	$u = Get-QADUser -objectAttributes @{employeeID=$eid}
	$m = Get-QADUser -objectAttributes @{employeeID=$mid}

	If (($u -ne $null) -and ($m -ne $null)){
		set-qaduser -identity $u -office $user.location -title $user.title -manager $m.dn 
	}   
Elseif ($u -eq $null){Write-Host "User $eid not found"}
Elseif ($m -eq $null){Write-Host "Manager $mid not found"}
}

Open in new window

Chris Dent

The LDAP filter was missing a closing bracket. It should have been:

"(&(objectcategory=person)(objectclass=person)(employeeid=$($user.employee)00))"

Chris

KenMcF

It looks alright, I am not able to test it right now but will later today.
Not sure hom many users you will be using in this script but using get-qaduser with just the employeeid will take a while to search becuase I do not think it is not an indexed attribute by default. Take a look at the LDAP filter used in my last post.

Snappa2000

ASKER

KenMcF,

I just got the LDAPFilter working, and ran some time trials. with two accounts to update:
LDAPFilter = 29sec
ObjectAttributes = 12sec

Looks like ill stick with the later.

Cheers

KenMcF

Snappa2000:

Just want to see if you had a chance to test? let me know if you run into any problems.

Snappa2000

ASKER

Thanks for all your efforts, I think I have a working solution and much to your direction KenMcF

Much Appreciated!