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