# Syncing Workday information to SharePoint profiles

The [Workday AD provisioner](https://docs.microsoft.com/en-us/azure/active-directory/saas-apps/workday-inbound-tutorial) is a wonderful thing: as your data changes in Workday, it is automatically synced to Active Directory using an expressive XPath language to provide field level customization. 

From an IT perspective this is a great advancement: on and offboarding events, as well as employee data changes are automatically pushed through: one less thing to think about and track with tickets.

As wonderful as it is, not everything in Workday will sync: for example, currently there is no support for syncing binary data such as [worker photos](https://docs.microsoft.com/en-us/azure/active-directory/saas-apps/workday-inbound-tutorial#can-i-provision-users-photo-from-workday-to-active-directory). Another example is adminstrative assistants. These assignments can be tracked in Workday but, while AD does have an [assistant attribute](https://docs.microsoft.com/en-us/windows/win32/adschema/a-assistant), there no support for pushing that through to AAD, let alone [SharePoint Online](https://sharepoint.uservoice.com/forums/330318-sharepoint-administration/suggestions/40415299-sync-assistant-attribute-from-azure-ad).

Here's a simple script to sync admins using Powershell, the SharePoint [PNP library](https://docs.microsoft.com/en-us/powershell/sharepoint/sharepoint-pnp/sharepoint-pnp-cmdlets?view=sharepoint-ps) and Workday SOAP APIs. With some simple modifications for automation variables the script will run using Azure Automation:

{{< highlight powershell >}}

$clientID = # client ID for SharePoint
$secret = # secret for Sharepoint
$username = # username for Workday Service Account
$password = # password for Workday Service acccount

# be sure to connect to the -admin version of your SharePoint to write to user profiles
Connect-PnPOnline -ClientId $clientID -ClientSecret $secret -Url "https://XXX-admin.sharepoint.com/" -WarningAction Ignore

# this is a naive implementation in that we hardcode the response count to be > 
# than the number of employees. If your org is bigger, implement paging
$fullRequest = [xml]@'
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bsvc="urn:com.workday/bsvc">
   <soapenv:Header>
   <wsse:Security soapenv:mustUnderstand='1' xmlns:wsse='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'>
        <wsse:UsernameToken>
            <wsse:Username></wsse:Username>
            <wsse:Password Type='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText'></wsse:Password>
        </wsse:UsernameToken>
    </wsse:Security>
   </soapenv:Header>
   <soapenv:Body>
        <bsvc:Get_Workers_Request bsvc:version="v35.2">
            <bsvc:Request_Criteria>
                <bsvc:Exclude_Inactive_Workers>1</bsvc:Exclude_Inactive_Workers>
            </bsvc:Request_Criteria>
            <bsvc:Response_Filter>
                <bsvc:Count>XX</bsvc:Count>
            </bsvc:Response_Filter>
   		    <bsvc:Response_Group>
                <bsvc:Include_Organizations>1</bsvc:Include_Organizations>
            </bsvc:Response_Group>
        </bsvc:Get_Workers_Request>
    </soapenv:Body>
</soapenv:Envelope>
'@

$request = $fullRequest

$request.Envelope.Header.Security.UsernameToken.Username = $username
$request.Envelope.Header.Security.UsernameToken.Password.innerText = $password
class Worker {
    [string] $employeeUPN
    [string] $employeeID
    [string] $adminName
    [string] $adminEmployeeID
    [string] $adminUPN
}
# replace XXX with your Workday services endpoint
$response = Invoke-WebRequest -useBasicParsing -Uri XXX `
    -Headers (@{SOAPAction='Read'}) -Method Post -Body $request `
    -ContentType application/xml
$content = [xml]$response.content    
$responseData = $content.Envelope.Body.Get_Workers_Response.Response_Data

$UPNLookup = @{}
# map ID to UserPrincipalName for foast lookups
$responseData.Worker | ForEach-Object {                                              
    $id = $_.Worker_Data.Worker_ID
    $UPNLookup[$id] = $_.Worker_Data.User_ID
}
# Iterate through workers on the response, use Xpath to pull the 
# fields you need from the XML response
$responseData.Worker | ForEach-Object {                                              
    $worker = [Worker]::new()
    $worker.employeeUPN = $_.Worker_Data.User_ID
    $worker.employeeID = $_.Worker_Data.Worker_ID
    $worker.adminName = select-xml -xml $_ -namespace @{'wd'='urn:com.workday/bsvc'} `
        -xpath "wd:Worker_Data/wd:Organization_Data/wd:Worker_Organization_Data/wd:Organization_Data[starts-with(wd:Organization_Reference_ID/text(),'Admin_Support_')]/wd:Organization_Name/text()"
    $worker.adminEmployeeID = select-xml -xml $_ -namespace @{'wd'='urn:com.workday/bsvc'} `
        -xpath "wd:Worker_Data/wd:Organization_Data/wd:Worker_Organization_Data/wd:Organization_Data[starts-with(wd:Organization_Reference_ID/text(),'Admin_Support_')]/wd:Organization_Support_Role_Data/wd:Organization_Support_Role/wd:Organization_Role_Data/wd:Worker_Reference/wd:ID[@wd:type='Employee_ID' or @wd:type='Contingent_Worker_ID']/text()"
    $worker.adminUPN = $UPNLookup[$worker.adminEmployeeID]
    if($worker.adminName.Length -gt 1){
        write-output "Will set $($worker.adminUPN) as admin for $($worker.employeeUPN)"
        set-pnpuserprofileproperty -Account $worker.employeeUPN `
            -Property 'Assistant' -Value "i:0#.f|membership|$($worker.adminUPN)"
    }
}

Disconnect-PnpOnline
{{< / highlight >}}

This script builds on the following posts:

 * https://www.techmikael.com/2018/08/modifying-terms-using-app-only-tokens.html (which covers the mystical app@SharePoint user)
 * https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azureacs - which describes why we can't use an Azure AD app registration
 * https://github.com/pnp/powershell/issues/277 - which describes the permissions needed

There's some obvious enhancements you might want to include for a large org:
 * use the request critera to only get workers whose data has changed (maybe with a param to indicate if full or incremental load)
 * implement paging so that the DOM parse of the XML response size doesn't get out of hand

I hope you find it useful.