MSEndpointMgr
Home » Azure » Log Analytics » Log Analytics & Update Compliance – Building A Windows Update Dashboard!

Log Analytics & Update Compliance – Building A Windows Update Dashboard!

With companies around the world co-managing devices and tenant attaching their devices, one thing I keep hearing from customers is how can we create our own reports and/or dashboards to facilitate the things that are important to me as an admin, or for those in management who need to see what is happening.

This question would have been solved in the past through custom SSRS reports or even PowerBI if you wanted to add some visualisation magic to reports and make them readily accessible to others. With devices now being in a mix of on-premises and in-cloud management states, this poses a bit more of a challenge.. or does it?

Log Analytics and Update Compliance FTW

In this post we are going to look at how we can use Log Analytics and Update Compliance to go beyond the native reporting in Intune or indeed Configuration Manager, and create a fully custom Windows Update dashboard.

To wet your appetite, lets just say we wanted to know about the following;

  • Patch compliance over time
  • Full list of KB’s installed, including success rates, failure rates, etc
  • Patching of KB’s over specific time periods
  • Feature update trends
  • Unsupported builds of Windows 10 in our environment
  • Windows 10 builds – per OEM manufacturer
  • Update sources
  • PrintNightmare Compliance

Does that sound good to you? Let me visualise this for you so you can see what this might look like.

Dashboard Header

Updates by age:

Feature update trending;

Windows Update Sources;

PrintNightmare Patch State;

If this is something that you think would be useful, then read on and I will get you started on the basics of how this information is obtained, and get you on the path to building your own custom dashboards.

Pre-Requisites

Before we set off on this build, let us get the pre-reqs out of the way.

  • Azure Subscription Required
  • Log Analytics Workspace Required

I’m not going to go through the process of setting up the above, Microsoft have this documented in detail here – Create a Log Analytics workspace in the Azure portal – Azure Monitor | Microsoft Docs.

My recommendation is to have your Intune Diagnostic logs and Azure AD logs also set to send information to the same Log Analytics workspace, so we can easily obtain information from these data sources without having to remember to set different subscriptions in queries.

Update Compliance

As most of you are aware, Microsoft uses telemetry data to help them understand how their products are used. The amount of data being sent can be controlled within the telemetry settings, but in general we are at very least sending information about the Windows build state.

With update compliance we can “tag” data from your organisation so that we can then query the data, which is kind of cool right? The level of detail being sent will impact your ability to report of course, so in examples on this post I am using the “enhanced” setting so that I can see as much as possible.

So let us get started and add Update Compliance from the marketplace to our Azure tenant;

  • Log onto the Azure Portal – https://portal.azure.com
  • Type in “Update Compliance” in the search bar and you should have a screen similar to the one below;
  • Click on Update compliance and it will now bring you to the following screen where you can select your Azure subscription, resource group, and log analytics workspace;
  • Continuing to the next screen, the validation process first checks everything is in place (permissions, billing etc)
  • Clicking on Create we should then have a visual indicator that the resource is being added. This process should only take a short time to complete;
  • Once completed, if you go to the Log Analytics workspace you added the solution to, we can obtain the commercial ID that we will then use to “tag” your telemetry data with

At this point we now have Update Compliance added, the next thing we need to do is to push out configuration to your Windows 10 client machines so they send the required level of telemetry data and associated the data with our commercial ID.

Client Configuration

The settings for update compliance in this example are going to be pushed out via Intune in the form of a Custom (CSP) policy.

  • Open the Endpoint Manager Admin Center – https://endpoint.microsoft.com
  • Click on Devices, Windows, and Configuration Profiles
  • Click on “+ Create Profile”, then select Windows 10 as the platform, Templates as the profile type, and Custom as the template name
  • Click on “Create”
  • Give your profile a name
  • Now we need to add some OMA-URI values to the policy;

Commercial ID

Name: CommercialID
Description: Commercial ID used for Update Compliance reporting
OMA-URI: ./Vendor/MSFT/DMClient/Provider/MS DM Server/CommercialID
Data Type: String
Value: %YOURCOMMERICALID%

Allow Telemetry

Name: AllowTelemerty
Description: Sets the diagnostic data level
OMA-URI: ./Vendor/MSFT/Policy/Config/System/AllowTelemetry
Data Type: Integer
Value:
3

Telemetry Opt-In Settings

Name: ConfigureTelemetryOptInSettingsUx
Description: Disables telemetry user interface
OMA-URI: ./Vendor/MSFT/Policy/Config/System/ConfigureTelemetryOptInSettingsUx
Data Type: Integer
Value: 1

Device Name Inclusion

Name: AllowDeviceNameInDiagnosticData
Description: Allows the computer device name in the analytics data
OMA-URI: ./Vendor/MSFT/Policy/Config/System/AllowDeviceNameInDiagnosticData
Data Type: Integer
Value: 1

Telemetry Change Notification

Name: ConfigureTelemetryOptInChangeNotification
Description: Disables change notification to telemetry settings from being displayed
OMA-URI: ./Vendor/MSFT/Policy/Config/System/ConfigureTelemetryOptInChangeNotification
Data Type: Integer
Value: 1

Update Compliance Processing

Name: AllowUpdateComplianceProcessing
Description: Allows update compliance processing post May 10, 2021
OMA-URI: ./Vendor/MSFT/Policy/Config/System/AllowUpdateComplianceProcessing
Data Type: Integer
Value: 16

Note: If you have deployed the above settings prior to May 10th of 2021, you will need to ensure this value is included going forward.

  • You should now have a policy similar to the one below:
  • Click on “Review + save” and assign the policy to a group containing all of your Windows 10 devices

If you are unsure of any element in the above and have security concerns about the level of data, please refer to the Microsoft documentation on configuring machines for Update Compliance here – Configuring Microsoft Endpoint Manager devices for Update Compliance – Windows Deployment | Microsoft Docs and the CSP settings documentation here – Policy CSP – System – Windows Client Management | Microsoft Docs

Hold Up – Update Compliance Data Delay

At this point it is worth noting that there will be a delay of 72 hours for your devices to start reporting data through to your Log Analytics workspace. We can of course configure the workbook, but my suggestion is you wait for this process to kick in first, so you can see data appearing and validate this as you go.

Time To Build A Dashboard

In this section we will run through how to first of all query the data in Log Analytics, then take the values we want and put them into a custom Workbook.

I know a lot of you will be asking me could I just share the Workbook with you, unfortunately though time and effort with customers means this workbook is IP of my employer and can’t be directly shared. I’m not going to give you a hard sell on that, as you know where to find me if you want to go down that road. What I am going to do though is give you some inspiration to get yourself started with this and who knows where this will go for you.

Lets get started then

  • The first thing we want to do is check to see if the data is being reported back in correctly (i.e with the computer names etc). So go back to your Log Analytics Workspace and click on “Logs”
  • Clicking out of the Queries prompt, you should now see a list of logs which you can obtain information from;
  • Querying the WaasDeployment table for instance, we can see the following information;

As you can see from the above, we now have detailed information on individual machines, KB’s being installed along with their type, the Windows build number etc. So the idea here is to take those values and summarise them in a method that makes this information easy to consume.

Create A Workbook / Dashboard

  • Click on Workbooks and then click on “New”, you should end up with something similar to the below;
  • So let us say you wanted to create a Patch Compliance over the last 30 day trendline, then we could use the WaaSUpdateStatus table to query the OSSecurityUpdateStatus values. As we can see by querying the log using the below query (be sure to set the “Time Range” to a value you require, 30 days for example)

WaaSUpdateStatus
| project TimeGenerated, Computer, OSSecurityUpdateStatus
| summarize dcount(Computer) by TimeGenerated, OSSecurityUpdateStatus

  • If we want to visualise the data over time, the area chart or time chart rendering options will display the data within the query going from the oldest to newest date;

WaaSUpdateStatus
| project TimeGenerated, Computer, OSSecurityUpdateStatus
| summarize dcount(Computer) by TimeGenerated, OSSecurityUpdateStatus|
| render areachart

  • Taking this back into our Workbook, we can add this visualisation by going to “Add Query”, inputting the query (without the render line) and then select the visualisation that you wish to present, in this example we are using “Area Chart”;
  • Now we have some additional controls in here that allow you to “spice up” these visualisations with bar charts, custom colouring etc. Taking an example that we want the “Up-to-date” values displayed as Green, and the “Not Up-to-date” displayed as Red, we simply click on “Chart Settings”
  • This now presents us with options for displaying the data;
  • Clicking on “Series Settings” we can now see the available returned values and do things like change the display name in order to make it easier to read, or change the colour;

So at this point we have done our first simply query to display patching over time. The graph of course will always have a peak and dip as updates are released, but you should be checking for that normal graph to ensure you know clients are being patched.

Give Me More Detail

Let us say you wanted to know about patch installation over a specific time period, and report back on the KB, and the success rates for installation then, we could get more complex in our query;

  • Bringing this into our workbook, and making some changes to the output and formatting we end up with something like the below;

What About The Donuts?

Everyone loves a good donut graph don’t they? Well we can also use that format to visualise something like whether or not we have unsupported builds of Windows in our environment, querying the WaaSUpdateStatus table.

  • The query for this one is again simple. As we look at the out put from the query and see the different types of OSFeatureUpdateStatus reported back;
  • Now we can use that same query to present a donut report for a defined period;

Some Useful Tips

When building your workbook / dashboard, if you want charts to render side by side, you need to specify the max percentage widths of the rendering;

Another tip is to use the “Full” size option, as using even medium or large can result in the graphs having a lot of white space around them.

Finally one last thing. Include links to Microsoft documentation so those reviewing the reports can understand their purpose and severity.

Providing Access To Your Workbook / Dashboard

To view workbooks and the data within, users must have at least the Log Analytics Reader role assigned on the Log Analytics Workspace. You can do this by following the below

  • Go into the Log Analytics Workspace in the Azure Portal
  • Click on Access Control (IAM)
  • Click on “+ Add”
  • Select the “Log Analytics Reader” role for those who just need to view the data, or “Log Analytics Contributor” role for those who need to add/edit workbooks;

Conclusion

I hope you are now inspired to start playing around with the data you can obtain from Log Analytics / Update Compliance, and start building your own awesome dashboards!. I would like to thank Jan Ketil Skanke @jankeskanke and Sandy Zeng @sandy_tsang for contributing to this post and the dashboard demonstrated here.

Thanks for reading.

(5502)

Maurice Daly

Maurice has been working in the IT industry for the past 20 years and currently working in the role of Senior Cloud Architect with CloudWay. With a focus on OS deployment through SCCM/MDT, group policies, active directory, virtualisation and office 365, Maurice has been a Windows Server MCSE since 2008 and was awarded Enterprise Mobility MVP in March 2017. Most recently his focus has been on automation of deployment tasks, creating and sharing PowerShell scripts and other content to help others streamline their deployment processes.

Sandy Zeng

Sandy is an Enterprise Mobility MVP since 2018. She is an experienced Information Technology Specialist for over 10 years. Skilled in Microsoft Endpoint Manager (ConfigMgr and Intune), Windows 10 and security. Sandy's interests are mostly related to Microsoft Technologies, she has passions learning new skill sets to improve her professional career and also as her hobbies. She uses her expertise to help customers achieve their goals and solve their issues.

Sandy founded the https://sandyzeng.com blog and is now a blogger on MSEndPointMgr.

Jan Ketil Skanke

Jan Ketil is an Enterprise Mobility MVP since 2016 and are working as a COO and Principal Cloud Architect at CloudWay in Norway. He has been in the industry for more than 20 years working for both Microsoft Partners and Microsoft. He loves to speak about anything around Enterprise Mobility and Secure Productivity. He is also the lead for the community conference Experts Live Norway. Jan Ketil has presented at large industry conferences like Microsoft Ignite, Microsoft Ignite The Tour, Microsoft Inspire, Experts Live Europe, Techmentor HQ (3rd best session 2019) and NIC Conference in Oslo.

3 comments

  • Hi Maurice… great article and it really encouraged me to dive into the update compliance logs.

    I took the liberty of copying out the ‘Give Me More Detail’ example you provided above, but every time I run it, I get vastly different numbers.

    I’m wondering if you could let me know if it’s my transcribing skills or something else.

    The query I’m using is:
    WaaSDeploymentStatus
    | where UpdateCategory == “Quality” and UpdateClassification == “Security”
    | where isnotempty (UpdateReleasedDate)
    | where UpdateReleasedDate between (ago(120d) .. now())
    | where isnotempty (Computer)
    | extend ReleaseName = strcat(OSVersion, ‘ – ‘, ReleaseName, ‘ – ‘, format_datetime(UpdateReleasedDate, ‘dd/MM/yyyy’))
    | summarize arg_max(UpdateReleasedDate, *) by Computer, ReleaseName
    | summarize Total = count(), Success = countif(DeploymentStatus == ‘Update completed’), Fails = countif(DeploymentStatus == ‘Failed’), Unknown = countif(DeploymentStatus == ‘Unknown’), [‘In progress’] = countif(DeploymentStatus == ‘In progress’), [‘Progress stalled’] = countif(DeploymentStatus == ‘Progress stalled’) by ReleaseName // Summarize the total, successful and failed operations by name
    | extend SuccessRate = (Success * 100 / Total) // Calculate the percentage of succesful operations against the total

    • Hello Donal, Thanks for trying out the query. Can you try this one, this should be now stable and I have updated the screenshot in our blog post.

      WaaSDeploymentStatus
      | where UpdateCategory == “Quality” and UpdateClassification == “Security”
      | where isnotempty(UpdateReleasedDate)
      | where UpdateReleasedDate between (ago(120d) .. now())
      | where isnotempty (Computer)
      | extend ReleaseName = strcat(OSVersion, ‘ – ‘, ReleaseName, ‘ – ‘, format_datetime(UpdateReleasedDate, ‘yyyy/MM/dd’))
      | summarize arg_max(TimeGenerated, *) by Computer, TargetBuild
      | summarize Total = count(),
      Success = countif(DeploymentStatus == ‘Update completed’),
      Fails = countif(DeploymentStatus == ‘Failed’),
      Unknown = countif(DeploymentStatus == ‘Unknown’),
      [‘In Progress’] = countif(DeploymentStatus == ‘In progress’),
      [‘Progress stalled’] = countif(DeploymentStatus == ‘Progress stalled’)
      by ReleaseName // Summarize the total, successful and failed operations by name
      | extend SuccessRate = (Success * 100 / Total) // Calculate the percentage of succesful operations against the total
      | order by ReleaseName

Sponsors

Subscribe

Do you want to be notified of new posts on our site?

Please enter your email address below:

Categories

MSEndpointMgr.com use cookies to ensure that we give you the best experience on our website.