UltiPro Sync User Attributes to Active Directory (CSV Method)

[NOTICE]

This is a completely valid method but not how we do it today. We use the Report-as-a-Service API to get more granular about the data we extract. It's a little more complex because it uses session logic vs. the simple logon approach used below. Both have their merits. But the RaaS option will certainly run much faster on large data sets. Please check out the next blog post for more info to compare your options.

Objective

Sync user attributes between UltiPro and Active Dirctory. Many of the values originate with HR. Items like Title, Department, EmployeeID and so on.

UltiPro has a relatively well documented taxonomy for their webservices / REST APIs.
https://connect.ultipro.com/documentation#/api/

It isn't the most intuitive page but you can find the model for each API mid-page. If the API accepts query parameters they'll be spelled out at the bottom. (see below)

Method

1. Use existing PowerShell skills to create scripting to pull/parse UltiPro web services data
2. Use Azure Automation to:
1. Secure the credentials for the Web Service Account
2. Control the job schedules
3. Connect to on-prem resources through Hybrid Worker
3. Create output that can be digested by sync tools like ManageEngine ADManager

Requirements

• Web Service Account with API Access. We used the following APIs with View-Only access.
• Employee Changes
• Employement Details
• Org Levels
• Customer API Key
• Customer tenant URL (varies slightly based on where you're hosted)

Before we get started...

Other Noteworthy Items

• ManageEngine does have a native integration --- HOWEVER, they require permission to APIs that include SSN and such. We opted not to use their API to keep the HR data as secure as possible.
• There are pagination limits in the APIs (this is common for REST). This is highlighted in the script.
• Any of this can be accomplished with Task Scheduler and cred keys. Azure just makes it a little easier and does a nice job logging.
• This does take time to process. Depending on the size of your organization, it could take quite a while. Ours takes about 20-30 minutes. This is due to some of the cross references between the different APIs being processed in memory.

Create a Credential

In your Automation Account > Credentials, add a credential for the Web Service API User

Create an Azure Runbook

I'm not going to go into great detail but this is just a generic PowerShell Runbook. Copy the script above into the runbook.

Schedule the Runbook Job

Set a reasonable schedule to allow time for it to finish but not so frequent that no updates are likely to be there or you get flagged for hammering the API. We set ours to 4 hours.

Add the Script to your Runbook

Replace these lines as appropriate:

Credentials

$webserviceaccount = Get-AutomationPSCredential -Name 'UltiPro Sync User'

$ADManagerRunAsAccount = Get-AutomationPSCredential -Name 'ADManager Runas Account'

Your Customer API Key #####

$headers =  @{'us-customer-api-key' = '#####'}

Your export file path

$exportpath = "\\FILEPATHHERE\UltiPro-User-Data"

$webserviceaccount = Get-AutomationPSCredential -Name 'UltiPro Sync User'

$ADManagerRunAsAccount = Get-AutomationPSCredential -Name 'ADManager Runas Account'

$exportpath = "\\FILEPATHHERE\UltiPro-User-Data"

$exportFile = '\Employee-Data.csv'

$headers =  @{'us-customer-api-key' = '#####'}

###########################################

#GET ACTIVE EMPLOYEES FROM ULTIPRO APIS

###########################################

#Collecting all active UltiPro users, 200 at a time. They do not support more than that through the API in one request.

#https://connect.ultipro.com/documentation#/api/817/Employment%20Details/get__personnel_v1_employment-details

write-Output "Searching Employment Details API for active employees"

$a=0

$employees = @()

$AgeObjCount = ""

do {

    $a++

    $tempObj = $null

    $URL = "https://service5.ultipro.com/personnel/v1/employment-details?employeeStatusCode!=T&primaryProjectCode=COMPUTER&per_page=200&page=$a"

    $tempObj = Invoke-RestMethod -Method 'Get' -Uri $url -Credential $webserviceaccount -headers $headers

    

    If ($tempObj.Count -ne 0)

    {$employees += $tempObj}

    else

    {$AgeObjCount = "end"}

} until ($AgeObjCount -eq "end")

$count = $employees.count

write-Output "Found $count employees"

###########################################################

#GET ORG LEVEL DETAILS FROM ULTIPRO APIS

###########################################################

#Org level description is not part of the normal employee details

#This lookup pulls the descriptions for later usage as "Department Name"

#https://connect.ultipro.com/documentation#/api/136

write-Output "Searching Org Level Details API for Company / Department Info"

#$OrgLevels = @()

$URL = "https://service5.ultipro.com/configuration/v1/org-levels"

$OrgLevels = Invoke-RestMethod -Method 'Get' -Uri $url -Credential $webserviceaccount -headers $headers

$SecondOrgLevels = $OrgLevels | Where-Object {$_.level -eq "2"}

###########################################################

#COLLECT ADDITIONAL EMPLOYEE DETAILS FOR ACTIVE EMPLOYEES

###########################################################

#Loop through all employees

#Load supervisor from AD based on the supervisor employee number

#Load name, address, etc. from the Employee Changes API

write-Output "Assembling employee details array"

$ObjectArray = New-Object System.Collections.Generic.List[System.Object]

foreach($employee in $employees)

{

    if($employee.supervisorEmployeeNumber.length -ne 0)

    {

        $manager = get-aduser -Filter * -Properties EmployeeID | Where-Object {$_.employeeID -eq $employee.supervisorEmployeeNumber} | Select-Object Name, distinguishedname

    }

    

    $employeeID = $employee.employeeID

    #Query Employee Changes API for specific employeeID vs. all employees

    #https://connect.ultipro.com/documentation#/api/199/Employee%20Changes/get__personnel_v1_employee-changes_%7BemployeeId%7D

    $URL = "https://service5.ultipro.com/personnel/v1/employee-changes/$employeeID"

    $employeeChanges = Invoke-RestMethod -Method 'Get' -Uri $url -Credential $webserviceaccount -headers $headers

    #Select only active records; it is possible that 1 employeeID can return multiple records if the person was terminated and rehired at a later date

    $employeeChangesActive = $employeeChanges | where-object {$_.employeeStatus -eq "A"}

    $department = $SecondOrgLevels | Where-Object{$_.code -eq $employee.orgLevel2Code}

    #Build temporary array to house data from difference sources

    $tempArray = New-Object System.Object

        $middle = ""

        if($employeeChangesActive.middleName.length -ne 0)

        {$middle = ($employeeChangesActive.middleName).substring(0, 1)}

        $WorkPhone = $null

        if($employeeChangesActive.workPhone)

        {$WorkPhone = "{0:+1 ###-###-####}" -f ($employeeChangesActive.workPhone -as [int64])}

        #Add attributes from employee-changes API

        $tempArray | Add-Member -MemberType NoteProperty -Name "givenName" -Value $employeeChangesActive.firstName

        $tempArray | Add-Member -MemberType NoteProperty -Name "middleName" -Value $middle

        $tempArray | Add-Member -MemberType NoteProperty -Name "sn" -Value $employeeChangesActive.lastName

        $tempArray | Add-Member -MemberType NoteProperty -Name "telephoneNumber" -Value $WorkPhone

        $tempArray | Add-Member -MemberType NoteProperty -Name "homePhone" -Value $employeeChangesActive.homePhone

        $tempArray | Add-Member -MemberType NoteProperty -Name "streetAddress" -Value $employeeChangesActive.employeeAddress1

        $tempArray | Add-Member -MemberType NoteProperty -Name "streetAddress2" -Value $employeeChangesActive.employeeAddress2

        $tempArray | Add-Member -MemberType NoteProperty -Name "l" -Value $employeeChangesActive.city

        $tempArray | Add-Member -MemberType NoteProperty -Name "st" -Value $employeeChangesActive.state

        $tempArray | Add-Member -MemberType NoteProperty -Name "postalCode" -Value $employeeChangesActive.zipCode

        $tempArray | Add-Member -MemberType NoteProperty -Name "physicalDeliveryOfficeName" -Value $employeeChangesActive.employeeAddress1

        #Add attributes from Organization Levels API

        $tempArray | Add-Member -MemberType NoteProperty -Name "department" -Value $department.description

        #Add attributes from employment-details API

        $tempArray | Add-Member -MemberType NoteProperty -Name "Title" -Value $employee.jobDescription

        $tempArray | Add-Member -MemberType NoteProperty -Name "Company" -Value $employee.companyName

        $tempArray | Add-Member -MemberType NoteProperty -Name "employeeID" -Value $employee.employeeNumber

        $tempArray | Add-Member -MemberType NoteProperty -Name "division" -Value $employee.orglevel1code

        $tempArray | Add-Member -MemberType NoteProperty -Name "extensionAttribute11" -Value $employee.orgLevel2Code

        $tempArray | Add-Member -MemberType NoteProperty -Name "extensionAttribute12" -Value $employee.orgLevel3Code

        $tempArray | Add-Member -MemberType NoteProperty -Name "extensionAttribute13" -Value $employee.employeeStatusCode

        $tempArray | Add-Member -MemberType NoteProperty -Name "employeeIDinUltiPro" -Value $employee.employeeID

    

        #Add Manager from AD referenced from employment-details API

        $tempArray | Add-Member -MemberType NoteProperty -Name "Manager" -Value $manager.distinguishedname

        #Potential future fields

        #$tempArray | Add-Member -MemberType NoteProperty -Name "preferredName" -Value $employeeChangesActive.preferredName

        #$tempArray | Add-Member -MemberType NoteProperty -Name "mail" -Value $employeeChangesActive.emailAddress

        #$tempArray | Add-Member -MemberType NoteProperty -Name "location" -Value $employeeChangesActive.workLocation

        #$tempArray | Add-Member -MemberType NoteProperty -Name "jobCode" -Value $employeeChangesActive.jobCode

    $objectarray.add($tempArray)

}

write-Output "Exporting aggregated employee list to CSV for consumption"

New-PSDrive -Name "L" -PSProvider FileSystem -Root $exportpath -Credential $ADManagerRunAsAccount

$ObjectArray | Export-CSV "L:\$exportFile" -NoTypeInformation

Remove-PSDrive -Name "L"

Create a ManageEngine Automation

This scoops up the CSV and maps the attributes exported from the script directly to the AD user attributes. Again, not going to go into the specifics. ManageEngine has supporting documentation for configuring their jobs.