UltiPro Sync User Attributes to Active Directory (Report-as-a-Service + SQL)

UltiPro has two methods of REST API access. For the base APIs, you can find my prior post. This post will deal with the custom "Report-as-a-Service" (RaaS) API. This allows HR staff to author custom reports via Cognos Analytics. Most commonly, they'll email these around or do FTP. Many people don't realize that these APIs exist and streamline data exchange with improved security.

For more info on setting up Azure Automation variables and where to find the API documentation, please see my original post. This post will concentrate on the scripting for RaaS as well as exporting the data to SQL rather than CSV.

RaaS

The process is documented but not especially intuitive in the UltiPro API library. The process requires 5 steps to retrieve a report. Some of these steps may seem redundant. Don't try to take shortcuts. I tried, it won't work.

1. Logon (create a session)
2. Get the Report List
3. Execute the Report
4. Retrieve the Report
5. Logoff (end the session)

The Script

Most of the parameters are at the start of the script. But be sure to update the SQL server names if you plan to use that section. Those aren't set as variables (don't remember why, maybe laziness).

You can break this into 4 distinct sections:

• UltiPro data retrieval
• AD data retrieval (fetch distinguished names of managers)
• SQL Import for UltiPro data
• SQL Import for AD data

*FYI, we just filter out certain employee IDs like "999999" which for us just indicate a contractor. Feel free to remove it.

SQL Setup

I worked with a DBA to create a DB and tables. I just provided a spreadsheet of columns, data types, and expected character limits. Be sure that your data set matches the destination.

Our DBA also created a stored procedure to call during the import that completely overwrites the data set every time. For our purposes, we didn't need to keep the data. We wanted it fresh every X hours.

We use two tables + a view

1. Contains the UltiPro
2. Contains the AD manager DN (so we can match UltiPro manager ID to a usable attribute.
3. The view combines the two (which we use ManageEngine AD Manager to process)

#initialize variables from automation library

$dataserviceUrl = Get-AutomationVariable -Name 'UltiPro Data Service URL'

$streamingserviceUrl = Get-AutomationVariable -Name 'UltiPro Streaming Service URL'

$ClientAccessKey = Get-AutomationVariable -Name 'UltiPro ClientAccessKey'

$UserAccessKey = Get-AutomationVariable -Name 'UltiPro UserAccessKey'

$apiuser = Get-AutomationPSCredential -Name 'UltiPro Web API User'

$reportname = Get-AutomationVariable -Name 'UltiPro Report Name'

#Extract secure password string from account storage to plain text

$username = $apiuser.username

$password = [System.Net.NetworkCredential]::new("", $apiuser.password).Password

#configure reusable http header

$headers =  @{'Content-Type' = 'application/soap+xml; charset=utf-8'}

#reset variables

$serviceID = ""

$token = ""

$InstanceKey = ""

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

#Logon to UltiPro Report-as-a-Service API#

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

write-output "Connecting to Report-as-a-Service API"

$body = @"

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">

    <s:Header>

        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/LogOn</a:Action>

        <a:To s:mustUnderstand="1">$dataserviceUrl</a:To>

    </s:Header>

    <s:Body>

        <LogOn xmlns="http://www.ultipro.com/dataservices/bidata/2">

            <logOnRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                <UserName>$username</UserName>

                <Password>$password</Password>

                <ClientAccessKey>$ClientAccessKey</ClientAccessKey>

                <UserAccessKey>$UserAccessKey</UserAccessKey>

            </logOnRequest>

        </LogOn>

    </s:Body>

</s:Envelope>

"@

[xml]$logon = Invoke-RestMethod -Method POST -Uri $dataserviceUrl -headers $headers -Body $body -Credential $cred

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

#         Set Session Specific IDs       #

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

write-output "Fetching session IDs"

$serviceID = $logon.Envelope.body.LogOnResponse.LogOnResult.serviceID

$token = $logon.Envelope.body.LogOnResponse.LogOnResult.Token

$InstanceKey = $logon.Envelope.body.LogOnResponse.LogOnResult.InstanceKey

write-output "Session ID: $serviceID"

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

#             Get Report List            #

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

write-output "Enumerating report list"

$body = @"

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">

    <s:Header>

        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/GetReportList</a:Action>

        <a:To s:mustUnderstand="1">$dataserviceUrl</a:To>

    </s:Header>

    <s:Body>

        <GetReportList xmlns="http://www.ultipro.com/dataservices/bidata/2">

            <context xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                <ServiceId>$serviceID</ServiceId>

                <ClientAccessKey>$ClientAccessKey</ClientAccessKey>

                <Token>$token</Token>

                <Status>Ok</Status>

                <StatusMessage i:nil="true"/>

                <InstanceKey>$instanceKey</InstanceKey>

            </context>

        </GetReportList>

    </s:Body>

</s:Envelope>

"@

[xml]$ReportList = Invoke-RestMethod -Method POST -Uri $dataserviceUrl -headers $headers -Body $body -Credential $cred

$reportscount = ($reportlist.Envelope.body.GetReportListResponse.GetReportListResult.reports.Report).count

write-output "Reports Found: $reportscount"

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

#             Execute Report             #

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

write-output "Excecuting AD Sync Report"

#Get report based on name defined in UltiPro

$reportPath = $ReportList.Envelope.body.GetReportListResponse.GetReportListResult.Reports.Report | `

    Where-Object{$_.reportname -eq "$reportname"} | `

    Select-Object -ExpandProperty ReportPath

#Specify delimiter in header (if not specified, defaults to XML)

$executeHeaders =  @{ `

      'Content-Type' = 'application/soap+xml; charset=utf-8'; `

      'US-DELIMITER' = ","; `

  }

#Configure XML Header/Body payload for Execute Invoke REST

$body = @"

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">

    <s:Header>

        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/ExecuteReport</a:Action>

        <a:To s:mustUnderstand="1">$dataserviceUrl</a:To>

    </s:Header>

    <s:Body>

        <ExecuteReport xmlns="http://www.ultipro.com/dataservices/bidata/2">

            <request xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                <ReportPath>$ReportPath</ReportPath>

                <ReportParameters/>

            </request>

            <context xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                <ServiceId>$serviceID</ServiceId>

                <ClientAccessKey>$ClientAccessKey</ClientAccessKey>

                <Token>$token</Token>

                <Status>Ok</Status>

                <StatusMessage i:nil="true"/>

                <InstanceKey>$instanceKey</InstanceKey>

            </context>

        </ExecuteReport>

    </s:Body>

</s:Envelope>

"@

[xml]$execute = Invoke-RestMethod -Method POST -Uri $dataserviceUrl -headers $executeHeaders -Body $body

#Set the report key retrieved during execution to be used in the report streaming steps next

$reportKey = $execute.Envelope.body.ExecuteReportResponse.ExecuteReportResult.ReportKey

write-output "ReportKey: $reportkey"

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

#             RetrieveReport             #

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

write-output "Fetching report results output"

#Set retrieval header and specify stream output

$retrieveReportURL = $streamingserviceUrl + '?EmploymentStatus=A'

$retrieveHeaders =  @{ `

    'Content-Type' = 'application/soap+xml; charset=utf-8'; `

    'Accept-Encoding' = 'gzip, deflate'; `

}

#Stream output SOAP body

$body = @"

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">

    <s:Header>

        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bistream/2/IBIStreamService/RetrieveReport</a:Action>

        <h:ReportKey xmlns:h="http://www.ultipro.com/dataservices/bistream/2" xmlns="http://www.ultipro.com/dataservices/bistream/2">$reportKey</h:ReportKey>

        <a:To s:mustUnderstand="1">$streamingserviceUrl</a:To>

    </s:Header>

    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

        <RetrieveReportRequest xmlns="http://www.ultipro.com/dataservices/bistream/2"/>

    </s:Body>

</s:Envelope>

"@

$response = Invoke-RestMethod -Method POST -Uri $retrieveReportURL -headers $retrieveHeaders -Body $body

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

#                LogOff                  #

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

write-output "Disconnecting session"

#Disconnect from the data service API

$body = @"

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">

    <s:Header>

        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/LogOff</a:Action>

        <a:To s:mustUnderstand="1">$dataserviceUrl</a:To>

    </s:Header>

    <s:Body>

        <LogOff xmlns="http://www.ultipro.com/dataservices/bidata/2">

            <context xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                <ServiceId>$serviceID</ServiceId>

                <ClientAccessKey>$ClientAccessKey</ClientAccessKey>

                <Token>$token</Token>

                <Status>Ok</Status>

                <StatusMessage i:nil="true"/>

                <InstanceKey>$InstanceKey</InstanceKey>

            </context>

        </LogOff>

    </s:Body>

</s:Envelope>

"@

Invoke-RestMethod -Method POST -Uri $dataserviceUrl -headers $headers -Body $body

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

#        Convert Base64 to Array         #

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

write-output "Converting report stream data from Base64 to array/table format"

#Convert output from Base64 to string data (comes out as XML from )

$bytes = [Convert]::FromBase64String($response.Envelope.body.StreamReportResponse.ReportStream)

$csvdata = [System.Text.Encoding]::ASCII.GetString($bytes)

$arraydata = $csvdata | convertFrom-csv

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

#             Find Managers              #

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

$managers = get-aduser -filter * -Properties employeeID,Manager | where-object {

    $_.employeeID -notlike "666666" -and `

    $_.employeeID -notlike "888888" -and `

    $_.employeeID -notlike "999999" -and `

    $_.employeeID -ne $null

} | Select-Object employeeID, Manager, distinguishedname

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

#          Output UltiPro to SQL         #

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

write-output "Creating empty SQL data table in memory"

#build an empty data table in the expected SQL format

[System.Data.DataTable]$table = New-Object('system.Data.DataTable')

$table.Columns.Add("givenName", "System.String")

$table.Columns.Add("middleName", "System.String")

$table.Columns.Add("lastName", "System.String")

$table.Columns.Add("telephoneNumber", "System.String")

$table.Columns.Add("homePhone", "System.String")

$table.Columns.Add("streetAddress", "System.String")

$table.Columns.Add("city", "System.String")

$table.Columns.Add("state", "System.String")

$table.Columns.Add("postalCode", "System.String")

$table.Columns.Add("physicalDeliveryOfficeName", "System.String")

$table.Columns.Add("department", "System.String")

$table.Columns.Add("Title", "System.String")

$table.Columns.Add("company", "System.String")

$table.Columns.Add("employeeID", "System.String")

$table.Columns.Add("division", "System.String")

$table.Columns.Add("orglevel2code", "System.String")

$table.Columns.Add("orglevel3code", "System.String")

$table.Columns.Add("employeeStatusCode", "System.String")

$table.Columns.Add("ManagerEmployeeID", "System.String")

$table.Columns.Add("preferredName", "System.String")

$table.Columns.Add("mail", "System.String")

$table.Columns.Add("location", "System.String")

$table.Columns.Add("jobCode", "System.String")

write-output "Adding report data to PowerShell SQL data table object"

foreach($aRow in $arraydata)

{

    $row = $table.NewRow()

        $row.givenName = $arow.FirstName

        if($arow.MiddleName.length -ne 0)

        {$row.middleName = $arow.MiddleName.Substring(0,1)}

        $row.lastName = $arow.LastName

        $row.telephoneNumber = $arow.WorkPhone

        $row.homePhone = $arow.HomePhone

        $row.streetAddress = $arow.Address

        $row.city = $arow.City

        $row.state = $arow.State

        $row.postalCode = $arow.Zip

        $row.physicalDeliveryOfficeName = $arow.Address

        $row.department = $arow.OrgLevel2

        $row.Title = $arow.Title

        $row.company = $arow.Company

        $row.employeeID = $arow.EmployeeNumber

        $row.division = $arow.OrgLevel1Code

        $row.orglevel2code = $arow.OrgLevel2Code

        $row.orglevel3code = $arow.OrgLevel3Code

        $row.employeeStatusCode = $arow.EmployeeStatus

        $row.ManagerEmployeeID = $arow.EmployeeNumberSupervisor

        #$row.preferredName = $arow.

        #$row.mail = $arow.

        #$row.location = $arow.Location

        #$row.jobCode = $arow.JobCode

        $table.Rows.Add($row)

}

$tablecount = $table.Rows.count

write-output "Discovered $tablecount employee records"

write-output "Connecting to SQL Instance"

#Create Connection string

$cnString = 'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBMaintenance;Data Source=SERVERNAMEHERE'

#define empty system objects for command and connection strings

[System.Data.SqlClient.SqlCommand]$cmd = New-Object('system.data.sqlclient.sqlcommand')

[System.Data.SqlClient.SqlConnection]$c = New-Object('system.data.sqlclient.sqlconnection')

#set $c to connection string and open connection

$c.connectionstring = $cnString;

$c.open();

write-output "Executing SQL stored procedure"

#configure command type as stored procedure

$cmd.Connection = $c;

$cmd.CommandType = [System.Data.CommandType]::StoredProcedure

$cmd.commandText = "dbo.ADSync_InsertAll"

$perfResultsParam = New-Object('system.data.sqlclient.sqlparameter')

#sql table type param

$perfResultsParam.ParameterName = "ADSyncT"

$perfResultsParam.SqlDBtype = [System.Data.SqlDbType]::Structured

$perfResultsParam.Direction = [System.Data.ParameterDirection]::Input

$perfResultsParam.value = $table

$cmd.parameters.add($perfResultsParam);       

$cmd.executeNonQuery(); 

write-output "Disconnecting from SQL Instance"

$c.close();

write-output "Data retrieval and import complete"

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

#          Output AD Data to SQL         #

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

write-output "Creating empty SQL data table in memory"

#build an empty data table in the expected SQL format

[System.Data.DataTable]$table2 = New-Object('system.Data.DataTable')

$table2.Columns.Add("employeeID", "System.String")

$table2.Columns.Add("distName", "System.String")

write-output "Adding data to PowerShell SQL data table object"

foreach($manager in $managers)

{

    $row = $table2.NewRow()

        $row.employeeID = $manager.employeeID

        $row.distName = $manager.distinguishedname

        $table2.Rows.Add($row)

}

$table2count = $table2.Rows.count

write-output "Discovered $table2count employee records"

write-output "Connecting to SQL Instance"

#Create Connection string

$cnString = 'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBMaintenance;Data Source=SERVERNAMEHERE'

#define empty system objects for command and connection strings

[System.Data.SqlClient.SqlCommand]$cmd = New-Object('system.data.sqlclient.sqlcommand')

[System.Data.SqlClient.SqlConnection]$c = New-Object('system.data.sqlclient.sqlconnection')

#set $c to connection string and open connection

$c.connectionstring = $cnString;

$c.open();

write-output "Executing SQL stored procedure"

#configure command type as stored procedure

$cmd.Connection = $c;

$cmd.CommandType = [System.Data.CommandType]::StoredProcedure

$cmd.commandText = "dbo.ADDist_InsertAll"

$perfResultsParam = New-Object('system.data.sqlclient.sqlparameter')

#sql table type param

$perfResultsParam.ParameterName = "ADDistT"

$perfResultsParam.SqlDBtype = [System.Data.SqlDbType]::Structured

$perfResultsParam.Direction = [System.Data.ParameterDirection]::Input

$perfResultsParam.value = $table2

$cmd.parameters.add($perfResultsParam);       

$cmd.executeNonQuery(); 

write-output "Disconnecting from SQL Instance"

$c.close();

write-output "AD Data retrieval and import complete"