Updating ServiceNow with Hardware Asset Data using Logic Apps

This is part 4 of the scenario Perform Automation Based on Device Enrollment in Microsoft Intune.

This article describes how to modify the previous step so that instead of sending an email, we create or update an asset record in ServiceNow.

Figure 1 – From Logic App to ServiceNow

Figure 1 is a simple representation, but there are actually several steps behind the scenes to make this work. Figure 2 provides a more in-depth description of the remaining work to complete this scenario.

Figure 2 – Update CMDB Logical Flow

As you can see from Figure 2, we have to perform some extra queries to understand which actions are appropriate. Also, the asset model is a required field when creating an asset, so we need to either query to get an existing model ID, or if no matching model is found, we must create a new Model. Once we know we have a model, we then create an asset and link it to the model.

The process of creating an asset also creates a CMDB record by default, with limited information. So in all cases, we will update the CMDB record with additional information.

Depending on your company’s expertise in Logic Apps vs. ServiceNow, you could perform all of these steps in ServiceNow as well. For this article, we will build it all in Logic Apps. Figure 3 shows the query to ServiceNow, and if the device already exists, update CMDB.

Figure 3 – If the serial number already exists, update the CMDB record.

Before building out the remainder of this flow, ensure you have a ServiceNow developer instance with a configured service account. Figure 7 in that article shows the service account and the delegated rights. Since this article will create models and assets, you must also add the roles named asset and model_manager, as shown in Figure.4

Figure 4 – Granting rights for model and asset management.

Building out the rest of the flow

Start by cloning your current DeviceEnrollment Logic App so that you have a backup, and then in your DeviceEnrollment Logic App, remove the send email step we created in the previous article.

List Records with Matching SerialNumber

First, we must query ServiceNow to see if this device already exists in the hardware asset table, and then we build a condition to take us down the right path for subsequent steps.

  1. Add a new action, choose ServiceNow, and then List Records.
  2. If this is the first time connecting to ServiceNow, you will be prompted to connect. Enter the service account and password that we created in How to: Obtain a ServiceNow Developer Instance and Configure API Integration.
  3. For Record Type, choose Hardware.
  4. For Query, note that you will be using the query language from ServiceNow. For this step, you will enter serial_number= and then insert the dynamic variable for SerialNumber, as shown in Figure 5.
  5. Verify the information in Figure 5.
Figure 5 – The ServiceNow List Records action
  1. Add a new action called Condition.
  2. For the Condition, we must count the number of records returned from the previous step in Figure 5. Click on the condition filter field, and click on Expression in the pop-up. Enter the following expression:

length(body(‘List_Records_with_matching_SerialNumber’)?[‘result’])

  1. If, by chance, you named the previous step for listing records from ServiceNow differently, be sure to update that expression.
  2. Verify the information as shown in Figure 6 and then select Update.
Figure 6 – Creating a Custom Expression
  1. After selecting Update, you should see the conditional step with a branch for true and false, as shown in Figure 7.
Figure 7 – Conditional branch based on whether the serial number already exists in the ServiceNow asset table.

Update Existing Record in CMDB

We have confirmed we have an asset, and for this article, we’re going to assume that all asset information is set correctly (I’ll follow up with another article that will give other considerations depending on your environment).

As mentioned previously, the Asset and CMDB records are paired together in ServiceNow-they are different records with their own attributes. However, by default, there are business rules in place so that when you touch one, you may impact the other.

Since we have machine fact information from Intune, let’s use that information to keep the CMDB accurate.

What is a machine fact? Machine fact is gold! It is data that humans haven’t touched, and haven’t had an opportunity to touch or make dirty. For example, when you query the win32_operatingsystem WMI class, you receive operating system details from the Windows system that are detected by the operating system (things like memory, operating system version, last bootup time, and more). These machine facts are your best source of information for use in reporting, asset management, and configuration management.

  1. Add a new action, choose ServiceNow, and then Update Record.
  2. Choose Record Type of Computer (this ties to the appropriate CMDB class).
  3. For System ID, choose the dynamic variable Configuration Item, which was obtained from the List Records with matching SerialNumber step.
  4. Since this data is coming from Intune, you can set the Discovery source as Intune (note that Intune is not a default option in ServiceNow – work with your ServiceNow team to add this option or choose an available value, such as Other Automated).
  5. Set other desired variables, as shown in Figure 8. For my example, I’m mapping the AzureADdeviceID to the CMDB attribute Object ID, as this will help with future scenarios for correlation, uniqueness, etc. I also use the Intune property lastSyncDateTime for the CMDB attribute Most recent discovery, which helps understand the activity of a device and can help with asset management. Also, as shown in Figure 8, you can see that Logic Apps automatically identified this as a potential array (although for the way we built this, you will always have an array of size one by the time you get to this point).
Figure 8 – The process to update an existing record in CMDB.

Creating a New Asset in ServiceNow

And now, to follow the conditional check for an existing record down the false branch. This process is a little more involved, as shown previously in Figure 2.

When creating a new asset in ServiceNow, you must specify a model for that asset. The Model table is a relational table to the asset, so if the model does not yet exist, you need to create the model first, or asset creation will fail. Figure 9 shows an overview of creating a new asset. First, we check if the model exists, and if not, create the model followed by the asset, and then update CMDB (you can think of them as dependencies for now – Model->Asset->CMDB configuration item).

Figure 9 – The process to create a new asset in ServiceNow (as well as update CMDB).

As we have walked through these steps a few times, the next few items will be abbreviated and show you the highlights of each action, without walking you through the details.

First, we must check if a model exists. We do this by querying the ServiceNow Hardware Model class. Notice this time I set Display System References to Yes. This will give me a human-friendly model name that I can use to compare against the model I received from Intune, instead of a model identifier that’s unique to ServiceNow. So build your query as shown in Figure 10 to query for name=<your dynamic variable for model>. And similar to our conditional for checking for an existing device by serial number, create a new conditional here with the following expression:

length(body(‘List_Records_-_see_if_Model_exists’)?[‘result’])

If, by chance, you named the previous step for listing models from ServiceNow differently, be sure to update that expression. Figure 10 shows the query and the conditional.

Figure 10 – The query and conditional to check if a model exists.

If the model exists, we follow the true branch and create an asset, followed by updating the (newly-created) CMDB configuration item, as shown in Figure 11. Notice the Create Asset action below uses the Create Record ServiceNow action, and the Update CMDB action uses the Update Record ServiceNow action. Notice for Model we pass the Sys ID, which is a unique identifier in ServiceNow for the model obtained from our model query step.

Figure 11 – Creating a new asset and updating CMDB for an existing model.

If our conditional check for model returns that the model does not exist, we must create the model first. Figure 12 shows the high-level steps and classes involved in creating the model. We then take the Sys ID from the Create Model Record step and use it when creating the asset record. Finally, we update the CMDB record for the newly-created asset (details are not shown below but are the same as the last step in Figure 11).

Figure 12 – Creating a new model, asset and updating CMDB.

Congrats! You now have an example Logic App for processing information from Intune and inserting it into ServiceNow! As mentioned, this is one way to achieve this task. ServiceNow provides multiple ways to integrate via API, but this is a great starting point with using Logic Apps.

If you missed it, check out the initial article for this scenario to see all the steps from beginning to end. I also posted sample templates in GitHub.

Happy Automating!

Greg

P.S. A big Thank You! to Donnie Taylor for helping me troubleshoot some of the pain around Logic Apps and the ‘standard’ alert coming out of Log Analytics – hopefully Logic Apps will build some magic to easily consume that payload in the future.

P.P.S. Also a big Thank You! to Greg Nottage for running through my draft article and providing feedback for how to make it more usable.

Processing an Azure Alert with a Logic App

This is part 3 in the scenario Perform Automation Based on Device Enrollment in Microsoft Intune.

This post describes how to take the Logic App webhook configured in Create a Webhook from Azure Alerts to a Logic App process and prepare the data to be sent to ServiceNow, as described in our scenario. For this post, we’ll just send information to an email (which many people have asked for already). We’ll save the process of sending the device information to ServiceNow for our next post.

Figure 1 – Processing data from the webhook

For this post, we focus on processing the data once it’s received in the webhook. Some of the receive data is a little complicated to process directly in Logic Apps, so it’s worth the effort to build an Azure Function to quickly process the desired data using PowerShell.

And that’s the high-level description. Now let’s get into the details for HOW to make this happen.

Prerequisites

To perform the steps in this post, complete all the steps and prerequisites in part 2, Create a Webhook from Azure Alerts to a Logic App.

Enroll a Device to Generate Test Data

You completed all the steps in part 2 to get the data into your Logic App, so go ahead and enroll a device in Intune to generate data we can use for testing.

  1. Perform the steps to Test Diagnostics Sent to Log Analytics from our previous post.
  2. After verifying the test in the previous step, navigate to Alerts in the Azure portal.

You may need to wait at the Alerts page here for FIVE minutes, as our alert rule runs on a five-minute interval.

  1. Click on Sev 4 (because that’s what we defined in the previous step), and you should see a “Fired” alert, as shown in Figure 2.
Figure 2 – Alert view
  1. Click on the alert named New Device Enrolled to view the details. Select the History tab to verify the action group was triggered, as shown in Figure 3.
Figure 3 – Alert Status
  1. Now that you’ve verified the action group was successfully triggered, view the Runs history on the Logic App (we named it DeviceEnrollment), as shown in Figure 4.
Figure 4 – Runs history summary information for a Logic App
  1. Click on the most recent run to view the details of the Logic app run. Click on When an HTTP request is received and observe OUTPUTS, as shown in Figure 5.
Figure 5 – Show details of the

At this point you have verified that enrollment data was successfully sent to the Logic App, and you’re ready to start enhancing the Logic App.

Processing Data with the Logic App

And finally, we can start processing data! Please note that since you successfully have the webhook receiving device enrollment data, the process of iteratively developing and testing is easy! As you can see in Figure 5, there is a Resubmit button, so you’re able to make changes to (and save) the Logic App and then Resubmit the previous test payload, so you don’t have to actually enroll a device each time.

At this time, hopefully, you’re still on the same page shown in Figure 6, where you can view the output of our enrollment test run-this is where we start this stretch of development.

Add Schema to the HTTP Processing Step

  1. Click on Show raw outputs to view the body of the webhook sent from Azure alerts, as shown in Figure 6.
Figure 6 – Raw output for webhook
  1. Edit the DeviceEnrollment Logic App, and expand the first (and only) step When a HTTP request is received.
  2. Click Use sample payload to generate schema, paste the sample alert schema from this page, and click Done. You should now see the Request Body JSON Schema as shown in Figure 7.
Figure 7 – The HTTP webhook after adding the JSON schema
  1. Click Save.

Create an Azure Function App to Process the JSON payload (Using PowerShell)

Unfortunately, the alert information is nested in a way that it’s not easy to extract the data (well, currently for me, it’s pretty much impossible to extract using only Logic Apps-let me know if you figure out how to do this with less pain). However, we can quickly extract the data using PowerShell in an Azure Function App. For reference, here’s sample input to the Azure Function App, and here’s the resulting output.

Special shout-out to @donnie_taylor for schooling me on pushing this data through an Azure Function App. We tried a lot of “Parse JSON” attempts in Logic Apps directly, and just couldn’t get the alerts appropriately parsed.

  1. From the Azure portal, create a new Function App.
  2. Select the Resource Group (the same group as the Logic App), name it something unique (I chose ParseDeviceEnrollHook, so that one isn’t available to you), and choose PowerShell Core for the runtime stack as shown in Figure 8.
Figure 8 – Creating a Function App
  1. Click Review & Create, then Create.
  2. Once created, open the resource.
  3. Navigate to Functions and click New Function.
  4. Chose HTTP trigger.
  5. Name the function HTTPTrigger and set the Authorization level to Anonymous as shown in Figure 9.
Figure 9 – Create a new Function
  1. Click Create to view the code.
  2. Paste the PowerShell code below into the Function App (overwrite all code in the window). Alternatively, you can grab the code from here.
using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)

# Write to the Azure Functions log stream.
Write-Host "PowerShell HTTP trigger function processed a request."

# Interact with query parameters or the body of the request.

#Take the  information from Azure Common Alert Schema for Log Analytics, 
# and get the query results into a format we can actually use
$columns = $Request.body.data.alertContext.SearchResults.tables.columns.name
$rows = $Request.body.data.alertContext.SearchResults.tables.rows

#Set name-value pairs for each Search Result
$arr = @()

if ($Request.body.data.alertContext.ResultCount -eq 1) {
    $hash = @{}

    for ($i = 0; $i -lt $columns.count; $i++)
    { 
        $hash.Add($columns[$i],$rows[$i])
    }
    $arr += $hash
}


else {
$rows | foreach {
$hash = @{}

for ($i = 0; $i -lt $columns.count; $i++)
    { 
        $hash.Add($columns[$i],$_[$i])
    }
        $arr += $hash
    }
}

#Ensure we have an array of the results, and convert to JSON
$arrResults = ConvertTo-Json @($arr) -Compress

if ($arrResults) {
    $status = [HttpStatusCode]::OK
    $body = $arrResults
}
else {
    $status = [HttpStatusCode]::BadRequest
    $body = "No Data."
}

# Associate values to output bindings by calling 'Push-OutputBinding'.
Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = $status
    Body = $body
})

  1. Save the Function App.

Call the Azure Function App from Logic Apps

At this point we’ve received a payload to our Logic App, and we’re ready to send it to the Azure Function App for processing.

  1. Open the DeviceEnrollment Logic App.
  2. Add a new action, and choose Azure Functions.
  3. Choose the Azure Function App you just created, called ParseDeviceEnrollHook. Continue by selecting the HTTPTrigger from the Actions tab, as shown in Figure 10.
Figure 10 – Choose the HTTPTrigger action from the ParseDeviceEnrollHook Function App.

Choose Body (the one that start with a capital “B)as the request body for Http Trigger. Notice as you mouse over Body, you see the detail triggerBody(). Set the Headers as shown in Figure 11.

Figure 11 – Select the HTTP Body and configure headers for the Azure Function App

Parse the JSON results from ParseDeviceEnrollHook

When the ParseDeviceEnrollHook Azure Function App runs, the resulting payload will contain an array of all devices sent from the query results from the Azure Alert trigger created in my previous post. View a sample payload here. The next step is to parse that data so we can process each alert.

  1. Add a new action, choose Data Operations and then Parse JSON.
  2. For Content, choose the Body of the ParseDeviceEnrollHook step.
  3. Paste the following JSON for the Schema field (note, this is the schema, and not a sample payload):
{
    "items": {
        "properties": {
            "Category": {
                "type": "string"
            },
            "OperationName": {
                "type": "string"
            },
            "Properties": {
                "type": "string"
            },
            "Result": {
                "type": "string"
            },
            "SourceSystem": {
                "type": "string"
            },
            "TenantId": {
                "type": "string"
            },
            "TimeGenerated": {
                "type": "string"
            },
            "Type": {
                "type": "string"
            }
        },
        "required": [
            "TimeGenerated",
            "OperationName",
            "Type",
            "TenantId",
            "Properties",
            "SourceSystem",
            "Result",
            "Category"
        ],
        "type": "object"
    },
    "type": "array"
}
  1. Verify the action with Figure 12.
Figure 12 – The Parse JSON action to parse the results of DeviceEnrollHook Azure Function

Parse the Properties attribute for each record

Review the sample payload and you will see that each returned record has a Properties attribute that contains more JSON, so we need to parse that next.

  1. Add a new action, choose Data Operations and then Parse JSON.
  2. For Content, choose the Properties variable of the previous Parse JSON step.
  3. Note that as soon as you select the Properties variable, the Logic App Designer realizes this is an array (based on the schema from the Parse JSON step) and automatically creates a For each loop.
  4. Paste the following JSON for the Schema property:
{
    "properties": {
        "AadDeviceId": {
            "type": "string"
        },
        "AadTenantId": {
            "type": "string"
        },
        "AccountId": {
            "type": "string"
        },
        "ActivityId": {
            "type": "string"
        },
        "DeviceId": {
            "type": "string"
        },
        "EnrollmentEndTime": {
            "type": "string"
        },
        "EnrollmentStartTime": {
            "type": "string"
        },
        "EnrollmentType": {
            "type": "string"
        },
        "EnrollmentTypeMessage": {
            "type": "string"
        },
        "EventId": {
            "type": "string"
        },
        "IsAutopilot": {
            "type": "string"
        },
        "IsDuringEsp": {
            "type": "integer"
        },
        "RelatedActivityId": {
            "type": "string"
        },
        "Result": {
            "type": "string"
        },
        "ScaleUnit": {
            "type": "string"
        },
        "ScenarioName": {
            "type": "string"
        },
        "Scope": {
            "type": "string"
        },
        "StartTime": {
            "type": "string"
        },
        "Status": {
            "type": "string"
        },
        "TimeDiff": {
            "type": "string"
        },
        "Timestamp": {
            "type": "string"
        },
        "UserId": {
            "type": "string"
        },
        "UserUPN": {
            "type": "string"
        },
        "Version": {
            "type": "string"
        }
    },
    "type": "object"
}

This gets us to a single row in the returned results from our original Azure Monitor Alert action. Congrats, you finally have most of the data you need. Following shows example data from the previous Parse JSON step:

{
  "EnrollmentStartTime": "2020-04-11T05:23:38.3791076Z",
  "EnrollmentEndTime": "2020-04-11T05:23:40.2646604Z",
  "TimeDiff": "2",
  "Status": "Success",
  "UserId": "f594b6b9-5419-45d8-9ecb-ea837a17aa07",
  "DeviceId": "7e4be3df-4cfa-46ab-97ea-306437eead0c",
  "EventId": "46801",
  "Scope": "EnrollmentSuccess",
  "UserUPN": "greg@ramseyg.com",
  "EnrollmentType": "10",
  "EnrollmentTypeMessage": "AutoEnrollment",
  "IsAutopilot": "False",
  "AadDeviceId": "a7f65347-18ce-4575-9f28-6e75ba9e059f",
  "IsDuringEsp": 0,
  "Version": "10.0.18363.0",
  "AadTenantId": "8f0a670f-cf43-4ab7-xxxxxxxxxxxxxxx",
  "ScenarioName": "Microsoft.Management.Services.Diagnostics.SLAEvents.EnrollmentStatusPageSLAEvent",
  "ActivityId": "7e4be3df-4cfa-46ab-97ea-306437eead0c",
  "RelatedActivityId": "7f32e1da-5f1b-47ca-8334-bd2254bd43ef",
  "Result": "0",
  "Timestamp": "2020-04-11T05:23:40.2646604Z",
  "StartTime": "0001-01-01T00:00:00",
  "AccountId": "2713e776-be5c-4951-b487-xxxxxxxxxxxx",
  "ScaleUnit": "AMSUA0402"
}

In this JSON, you see information like AADDeviceID, IsAutopilot, etc. But for our scenario, we need additional information, such as Computer Name, Manufacturer, Model, and more. So our next step is to query that information from Intune, using Microsoft Graph.

Query Microsoft Graph to obtain Intune device properties

If you followed my previous blog on How to Use Logic Apps to Query Intune for Device Information, you have a head start for this step. If you have not, review that post to verify you have created the app registration and have the credentials required to query Graph for Intune.

  1. Add a new action, choose HTTP and then HTTP (again)..
  2. Choose GET for the Method.
  3. For the URI, enter the following: https://graph.microsoft.com/v1.0/deviceManagement/managedDevices/. Next, insert your cursor at the end of the line and select DeviceID as shown in Figure 13.
  4. For Headers add “Content-Type” as “application/json” as shown in Figure 13.
  5. Click Add new parameter, select Authentication, and then click off of the dialog to add the Authentication Type to the step.
  6. Select Active Directory OAuth for the Authentication type.
  7. Add your TenantClient ID and Secret (the ones you copied earlier while creating the App registration).
  8. For Audience, enter https://graph.microsoft.com.
  9. Review settings and compare them to Figure 13.
Figure 13 – HTTP Get to query Intune for a device

Parse the results of GetManagedDevices

Congrats! You’re almost there!. Next we need to parse the graph query results from the previous step. Review the sample payload for the graph query and complete the following steps:

  1. Add a new action, choose Data Operations and then Parse JSON.
  2. For Content, choose the Body variable of the previous HTTP query step.
  3. Paste the following JSON for the Schema property:
{
    "properties": {
        "@@odata.context": {
            "type": "string"
        },
        "activationLockBypassCode": {},
        "androidSecurityPatchLevel": {},
        "azureADDeviceId": {
            "type": "string"
        },
        "azureADRegistered": {
            "type": "boolean"
        },
        "complianceGracePeriodExpirationDateTime": {
            "type": "string"
        },
        "complianceState": {
            "type": "string"
        },
        "configurationManagerClientEnabledFeatures": {},
        "deviceActionResults": {
            "type": "array"
        },
        "deviceCategoryDisplayName": {
            "type": "string"
        },
        "deviceEnrollmentType": {
            "type": "string"
        },
        "deviceHealthAttestationState": {},
        "deviceName": {
            "type": "string"
        },
        "deviceRegistrationState": {
            "type": "string"
        },
        "easActivated": {
            "type": "boolean"
        },
        "easActivationDateTime": {
            "type": "string"
        },
        "easDeviceId": {
            "type": "string"
        },
        "emailAddress": {
            "type": "string"
        },
        "enrolledDateTime": {
            "type": "string"
        },
        "exchangeAccessState": {
            "type": "string"
        },
        "exchangeAccessStateReason": {
            "type": "string"
        },
        "exchangeLastSuccessfulSyncDateTime": {
            "type": "string"
        },
        "freeStorageSpaceInBytes": {
            "type": "integer"
        },
        "id": {
            "type": "string"
        },
        "imei": {},
        "isEncrypted": {
            "type": "boolean"
        },
        "isSupervised": {
            "type": "boolean"
        },
        "jailBroken": {
            "type": "string"
        },
        "lastSyncDateTime": {
            "type": "string"
        },
        "managedDeviceName": {
            "type": "string"
        },
        "managedDeviceOwnerType": {
            "type": "string"
        },
        "managementAgent": {
            "type": "string"
        },
        "manufacturer": {
            "type": "string"
        },
        "meid": {},
        "model": {
            "type": "string"
        },
        "operatingSystem": {
            "type": "string"
        },
        "osVersion": {
            "type": "string"
        },
        "partnerReportedThreatState": {
            "type": "string"
        },
        "phoneNumber": {},
        "remoteAssistanceSessionErrorDetails": {
            "type": "string"
        },
        "remoteAssistanceSessionUrl": {
            "type": "string"
        },
        "serialNumber": {
            "type": "string"
        },
        "subscriberCarrier": {
            "type": "string"
        },
        "totalStorageSpaceInBytes": {
            "type": "integer"
        },
        "userDisplayName": {
            "type": "string"
        },
        "userId": {
            "type": "string"
        },
        "userPrincipalName": {
            "type": "string"
        },
        "wiFiMacAddress": {
            "type": "string"
        }
    },
    "type": "object"
}

Now that you have the device details in your Logic App, you’re UNSTOPPABLE!. To complete this post, we’ll simply send an email.

Send an email about device enrollment details

  1. Add a new action, choose Office 365 Outlook and then Send an email (V2).
  2. Complete your standard To and Subject lines, and feel free to add any of the properties from our previous Parse JSON step. For my example Logic App, I selected the following:
    • deviceName
    • managedDeviceOwnerType
    • enrolledDateTime
    • operatingSystem
    • complianceState
    • managementagent
    • osVersion
    • emailAddress
    • azureADDeviceID
    • model
    • manufacturer
    • serialNumber
    • userDisplayName
    • userPrincipalName
  3. Following my example, insert the dynamic property that matches each of the items above, as shown in Figure 14.
Figure 14 – Send an test email with device attributes
  1. Save your work, cross your fingers, and Resubmit one of your previous test runs, or enroll new devices to see your process in action! Remember that when you enroll a new device, it may take approximately 10 minutes for the information to flow from Log Analytics to Azure Monitor to your Logic App. If all goes well, you should receive an email similar to Figure 15.
Figure 15 – Sample device enrollment email.

Figure 16 gives you an idea of what the Logic App should look like from start to finish:

Figure 16 – Parse alert and send email, end to end.

Congrats! That was a long haul, but hopefully a great learning experience. I realize there are a lot of steps here, so you may need to walk through this a couple of times to work the kinks out. DeviceEnrollment_SendEmail – LogicApps Template-Sanitized.json is a sample template.

My next blog in this series will show how to use the Logic App to insert and update asset records in ServiceNow – stay tuned and happy automating!

 

Greg