Introduction

I’ve recently found myself needing to interact heavily with Exchange Online in order to rework our system of Distribution List management within ServiceNow to resolve concerns around its scalability. I wanted to share the experience here and note some of the concepts I learned along the way.

Our Use Case

We’ve been on ServiceNow for a few years now and over that time we have been managing and automating access to Distribution Lists (DLs). The system was ported from our previous Service Management platform into ServiceNow during our implementation and we implemented it in ServiceNow with the then new kid on the block, Flow Designer, to pull data in the data from Exchange Online and push it into particular tables.

The Problem

At the time, it was not designed to use the Import Set functionality as we were very new to the system and had deadlines to meet so it instead used arrays that would vary from relatively small and manageable to huge and unwieldy to take data from a PowerShell Step and push the outputs to tables using a Script Step. This system worked very well for us at the time, but as we started to grow, we began to realize some cracks in the design. We were first tipped off to the issues when we noticed complaints of system slowness toward the end of business, which was around the time we ran jobs that handled large Distribution List automated entitlement. The performance challenge was traced by ServiceNow Support caused by low memory issues on our nodes and was caused by those same large jobs. With those reports, we were able to stem the issue by spinning the larger jobs off into even smaller pieces in order to give the system a bit more breathing room during their runs along with pushing the start time of the jobs farther back to lessen the impact as much as possible.

With the solution in place, we unfortunately still saw cases opened by ServiceNow Support reporting the same issue, but at the very least it was not consistent, only happening when other non-DL related jobs overlapped, nor was it impacting user experience much anymore. However, as we started to ramp up efforts to nearly double our size, we realized that we needed to focus our attention on fully resolving these performance challenges.

Plan of Action

When we began to design our new DL automation processes, we set out with several goals:

Significantly reduce our reliance on in-memory processing and replace it with Import Sets.
Increase flexibility of related Flow Actions (Get DLs, Get DL Membership, Add/Remove DL Entitlement, etc.).
Increase entitlement tracking accuracy (due to changes in Exchange, new user’s entitlements were not reconciling properly in ServiceNow).
Simplify integration management.
Create a framework for scoped application integrations going forward.

Moving to Import Sets and Increasing Flow Action Flexibility

The first major milestone in this rework was re-creating and extending the Flow Actions that pulled the data from Exchange Online and having them utilize native Import Set functionality in ServiceNow. The original versions could only handle single inputs and used a script step to push the output of the PowerShell commands directly to production tables.

In order to improve on the Action’s capabilities, we opted to:

Allow comma separated lists of inputs wherever possible
Have the action only be responsible for pulling the data and pushing it into Import Tables
Provide configuration options via system properties for key parts of the action

I’ll provide an example of pulling Distribution Lists here - all the other actions follow similar logic to this:


$warningPreference = "SilentlyContinue"
$ExchangeSecurePass = ConvertTo-SecureString -String $exchangepass -AsPlainText -Force
$ExchangeCred = New-Object System.Management.Automation.PSCredential -argumentlist $exchangeuser, $ExchangeSecurePass
$tableName = # Import set table target

# Set required TLS protocol
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

try {
    Connect-ExchangeOnline -Credential $ExchangeCred -ShowBanner:$false
    if ($SyncType -eq 'full') {
        $FileName = 'dlimport.csv'
        $dlCSV = (
            Get-DistributionGroup -ResultSize Unlimited -Filter "RecipientType -eq 'MailUniversalDistributionGroup'" | 
            Select-Object -Property @{l='DisplayName'; e={$_.DisplayName -creplace '\P{IsBasicLatin}'}},@{l='WindowsEmailAddress'; e={$_.WindowsEmailAddress -creplace '\P{IsBasicLatin}'}}, @{l='DistinguishedName'; e={$_.DistinguishedName -creplace '\P{IsBasicLatin}'}},WhenChanged,WhenCreated,Guid | 
            ConvertTo-Csv -NoTypeInformation
            )
    }
    elseif ($SyncType -eq 'incremental') {
        $Increment = (Get-Date).AddDays($Days)
        $FileName = 'incdlimport.csv'
        $dlCSV = (
            Get-DistributionGroup -ResultSize Unlimited -Filter "RecipientType -eq 'MailUniversalDistributionGroup' -and WhenChanged -gt '$Increment'" | 
            Select-Object -Property @{l='DisplayName'; e={$_.DisplayName -creplace '\P{IsBasicLatin}'}},@{l='WindowsEmailAddress'; e={$_.WindowsEmailAddress -creplace '\P{IsBasicLatin}'}}, @{l='DistinguishedName'; e={$_.DistinguishedName -creplace '\P{IsBasicLatin}'}},WhenChanged,WhenCreated,Guid | 
            ConvertTo-Csv -NoTypeInformation
        )
    }
    elseif ($SyncType -eq 'targeted') {
        $FileName = 'singleimport.csv'
        $dlCSV = (
            $DLEmails -Split ',' |
            ForEach-Object { Get-DistributionGroup -ResultSize Unlimited -Identity $_ |
                Select-Object -Property @{l='DisplayName'; e={$_.DisplayName -creplace '\P{IsBasicLatin}'}},@{l='WindowsEmailAddress'; e={$_.WindowsEmailAddress -creplace '\P{IsBasicLatin}'}}, @{l='DistinguishedName'; e={$_.DistinguishedName -creplace '\P{IsBasicLatin}'}},WhenChanged,WhenCreated,Guid } | 
            ConvertTo-Csv -NoTypeInformation
        )
    }
    Disconnect-ExchangeOnline -Confirm:$false
}
catch {
    $_
    throw "Unable to get DL information"
}


# Build SN headers
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $snuser, $pass)))
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add('Content-Type', 'multipart/form-data')
$headers.Add('Authorization', ('Basic {0}' -f $base64AuthInfo))
$headers.Add('Accept', 'application/json')

# Build SN POST request
$uri = "https://" + $instanceName + ".service-now.com/sys_import.do?sysparm_import_set_tablename=" + $tableName + "&sysparm_transform_after_load=true"
$method = "POST"
$Boundary = [System.Guid]::NewGuid().ToString()
$LF = "`r`n"
$FileEnc = $dlCSV -join $LF
$BodyLines = (
    "--$Boundary",
    "Content-Disposition: form-data; name=`"csvfile`"; filename=`"$FileName`"",
    "Content-Type: text/csv$LF",
    $FileEnc,
    "--$Boundary--$LF"
) -join $LF

try {
    $response = Invoke-Restmethod -Method $method -Headers $headers -ContentType "multipart/form-data; boundary=`"$Boundary`"" -Uri $uri -Body $BodyLines -ErrorAction Stop
    $response | ConvertTo-Json
}
catch {
    $_
    throw "Unable to import Data"
}

One key thing to note:

The creplace in the Select-Object cmdlet parameters removes any non-ASCII characters from the Display Name and Windows Email Address fields.

This is because we found that there are some cases where there are unintentional Unicode characters in those fields. If one were to slip through, it’s possible the offending row is ignored or skipped.

The solution above, and the others that follow in its footsteps significantly decreased our reliance on arrays and instead pushed the burden of importing transforming data back to the proper system that can handle the task - which in turn reduced the overall performance impact of our Exchange Integration to a point where it can run at nearly any time without causing user experience impact.

Increasing Entitlement Accuracy

Microsoft introduced a change in April ‘22 that caused the name parameter of any new user to become an ExternalDirectoryObjectId (EDOID). In our environment, we stored a different unique value that our integration then used to coalesce to a sys_user record. This put us into a situation where our entitlement tracking tables were not keeping track of any new user’s distribution list entitlements. During the course of remaking the Exchange Integration, we decided to pull the user’s mailbox GUID to use as a coalesce value to sys_user instead, as that is a much more stable field and source of truth. That minor change allowed hundreds of users to finally have accurate accounting of their Distribution List entitlements.

Simplifying Management

One of the top things on our minds as we created this custom application was to ensure it was easy for anyone to go configure without needing to change any code. To do this, we made use of System Properties to make levers for administrators to adjust whenever needed in order to adjust the behavior of the integration. One primary example is the $Days variable in the above PowerShell script. Additionally, we focused on ensuring the integration is easily extensible by future developers by setting a standard for in-line and external documentation.

Conclusion

Overall, creating this application exposed us to a new way of thinking when it comes to creating custom integrations on the platform. It gave us an opportunity to prove out creating Scoped Applications, which I now think are a great way to compartmentalize integrations with other systems. It also opened our eyes to how we can make integrations configurable and simple enough for anyone to understand with only a small amount of direction.

Resolving System Performance Issues - Exchange Online Integration