Alternatives for querying and visualizing Update Compliance data

This week is follow-up on the post of last week about enhancing Update Compliance with a custom Workbook in Microsoft Endpoint Manager admin center. There were multiple questions on that post regarding alternatives for querying and visualizing the Update Compliance data. The good news is that there are actually multiple alternatives for querying Update Compliance data, but, in all fairness, all the alternatives rely on the same API. The Azure Log Analytics REST API. That API can be called by specifying the workspace, providing a token and running the required query. Pretty straight forward. Also, that API is an important part of most other methods that are used for querying Update Compliance data. This post will provide a quick introduction to the Azure Log Analytics REST API, followed with a few different methods of retrieving Update Compliance information.

Introduction to the Azure Log Analytics REST API

The Azure Log Analytics REST API is an API that lets IT administrators programmatically execute Azure Log Analytics queries. That enables organizations to build custom visualizations and to extend the capabilities of Log Analytics. To call the API, the IT administrator only needs the Workspace ID, an authentication token and a query. The combination of those items will be the complete call to the API. The most challenging configuration for programmatically calling the API, is the authentication and the authorization. For the more standard solutions that’s pretty much covered already in a user friendly format – think about PowerShell or Power BI – but with true custom build solutions that requires a method to retrieve a token with the correct authorizations. That can be achieved by using an app registration with the permissions to read data via the Azure Log Analytics REST API and with the permissions to the Update Compliance workspace. To achieve that, simply walk through the following two steps.

  1. Step 1 – Create an app registration with the required app permissions
    • Open the Azure portal, navigate to Azure Active Directory > App registrations and create a new app registration
    • With Name, provide a unique name to later clearly identify the app registration
    • Navigate to API permissions, provide the app registration with at least Application permissions of Data.Read for Log Analytics API and grant admin consent for those permissions, to make sure that the authorization is provided with the right permissions and API
    • Navigate to Certificates & secrets, create a new secret and save the value for requesting a token
  2. Step 2 – Provide the app registration with the required permissions to the Update Compliance workspace
    • Open the Azure portal, navigate to Log Analytics workspaces and select the Update Compliance workspace
    • Navigate to Access control (IAM) and add a role assignment for the created app registration with at least the Log Analytics Reader role

The creation of the app registration provides the missing pieces of information to request a token with the required authorizations for usage with the Azure Log Analytics REST API and the Update Compliance workspace.

Using Postman with Update Compliance

Postman is an often used tool for testing calls on an API during the development of a custom build solution. That can also be the case for testing calls on the Azure Log Analytics REST API. Testing calls to that API with Postman can be achieved by simply walking through the following two steps.

  1. Step 1 – Retrieve a token via the https://login.microsoftonline.com/{tenantId}/oauth2/token endpoint
    • Open Postman, navigate to a new tab select POST and specify the endpoint
    • Navigate to Body, select x-www-form-urlencoded and specify the following keys and values (see also Figure 1)
      • grant_type: client_credentials
      • client_id: [the Application (client) Id of the earlier created app registration]
      • client_secret: [the Client secret of the earlier created app registration]
      • resoruce: https://api.loganalytics.io
  1. Step 2 – Query the https://api.loganalytics.io/v1/workspaces/{UpdateComplianceWorkspaceId}/query enpoint
    • Open Postman, navigate to a new tab select POST and specify the endpoint
    • Navigate to Authorization, select Bearer Token as the type and specify the earlier retrieved token
    • Navigate to Body, select raw > JSON and specify the query in JSON-format as shown below (see also Figure 2)
{
    "query": "WaaSUpdateStatus | summarize arg_max(TimeGenerated, *) by ComputerID"
}

For testing different queries with Postman, simply adjust the query in the body of the post request. Those queries can be as generic or complex as needed. The result might be a bit difficult to read the first time, as it first names the columns and than the rows. Keep that in mind with building a custom solution. This can be the starting point of a custom solution.

Using Power BI with Update Compliance

Power BI is nowadays one of the first products that comes to mind when thinking about visualizing data. The good thing is that it can also be used in combination with the Update Compliance data. Log Analytics provides a nice option to export a query and to import the query in Power BI Desktop. That can be achieved by simply following the two steps below. The detailed reader will notice that this method also uses the Azure Log Analytics REST API (see also Figure 4).

  1. Step 1 – Export the query in Log Analytics
    • Open Log Analytics workspace and select the Update Compliance workspace
    • Navigate to Logs and specify the query that contains the required data and select Export > Export to Power BI (M Query) (see also Figure 3)
  1. Step 2 – Import the query in Power BI Desktop
    • Open Power BI Desktop, select Get data > Blank query and select Query > Advanced Editor
    • Specify the exported query and make sure to close and apply the query (see Figure 4)

Once the query collected results, the power of Power BI comes to the table. Within a couple of minutes it’s possible to create a few treemaps – or any other type of visualization that displays some details. That best part in my opinion is the integration between the different treemaps. Selecting information in one treemap also highlights the devices in the other treemaps (see Figure 5). Just brilliant.

Using PowerShell with Update Compliance

PowerShell is also a nice method for programmatically querying the Update Compliance data. Luckily, the Azure Log Analytics REST API also comes with a cmdlets that can be used for querying the API. That cmdlet is part of the Az.OperationalInsights module. So, the first step is to install that module by running the command below.

Install-Module -Name Az.OperationalInsights

The next step is to connect and authenticate with Azure. That can be achieved by using the Connect-AzAccount cmdlet. When having multiple subscriptions, make sure to also specify the right subscription by running the command below.

Connect-AzAccount -Subscription {subscriptionId}

Once authenticated, the last step is to actually query the API. That can be achieved by using the Invoke-AzOperationalInsightsQuery cmdlet. Specify the Update Compliance workspace id and the query by running the command below.

Invoke-AzOperationalInsightsQuery -WorkspaceId "{UpdateComplianceWorkspaceId}" -Query "WaaSUpdateStatus | summarize arg_max(TimeGenerated, *) by ComputerID" 

Once the query results are collected, any PowerShell skills can be used to make something fancy of the data. Whether it’s a quick query to verify some information, or a custom solution for visualizing the data, anything is possible.

More information

For more information about the Azure Log Analytics REST API, refer to the docs about the Azure Log Analytics REST API.

1 thought on “Alternatives for querying and visualizing Update Compliance data”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.