Author: Steve July 10, 2013 0 Comments

We have a web based learning management system for our employees and have encountered challenges with the system ever since they applied an update.  Long story short, we needed to script for importing into Active Directory the Manager for employees from a CSV file exported from our HR system.
The export from our HR system is in the following format…

//first_name,surname,job_reference,job_title,department_reference,department,work_phone,work_extension,email_address,employee_number,managerfirstname,managersurname,managerjobtitle,employment_status//

For scripting stuff with AD I definitely recommend using Powershell scripts with the Quest ActiveRoles Management Shell for AD add on(Quest Software has been bought by Dell by the way).  I was originally playing around with reading in the CSV export in our nightly batch, pulling in the users from AD, matching up the manager for each and then outputting the data in a CSV.  This was problematic and took forever.  I decided to break things up and have a separate scheduled task that will run weekly to populate the managers field in AD. After poking around on the web for a bit and borrowing and modifying some scripts, with some trial and error I came up with the following…

clear-host
if ( (Get-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue) -eq $null )
{
    Add-PsSnapin Quest.ActiveRoles.ADManagement
}

$users = import-csv c:\util\LMS_Export_test.txt  

foreach ($user in $users){ 
    $eid = $user.employee_number
    $eid = “$($eid.substring($eid.Length-4,4))”
    $mfid = $user.managerfirstname
    $mfshort = “$($mfid.substring($mfid.Length3,3))”
    $msid = $user.managersurname

    $u = Get-QADUser -LdapFilter “(employeeNumber=$eid)”
    $m = Get-QADUser -LdapFilter “(givenName=$mfshort*)(sn=$msid)”

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

Worked well, I tested it on a subset of the export list and omited the ‘set-qaduser’ line and added the following above the If (($u ne $null~ line…

   Write-Host “User $eid”
   Write-Host “manager $msid, $mfid and short $mfshort”

Which allowed me to confirm it was working correctly before writing anything to AD.

Now I just need to modify my AD export script to include the manager field.

Steve