Enhance Update Compliance with a custom Workbook in Microsoft Endpoint Manager admin center

This week is all about enhancing Update Compliance by using a custom Workbook within the Microsoft Endpoint Manager admin console. The Update Compliance Workbook. That Update Compliance Workbook enables the IT administrator to get a quick view on the most important details. Besides that, adding that Update Compliance Workbook in the Microsoft Endpoint Manager admin center enables the IT administrator to pin the different queries of that Update Compliance Workbook to the dashboard. That provides the IT administrator with a dashboard that contains all the status information about the Microsoft Intune environment and a quick overview of the update status of the Windows 10 devices within that environment. This post provides that Update Compliance Workbook with the most important status information coming from the Update Compliance solution. The following will be addressed in this post.

Note: The provided example Update Compliance Workbook is created with the help of the early testers. Thank you for the feedback Jason, Stephane, Alex, Justin, Bruce, Carl, Nickolaj and Katy!

Prerequisites for the Update Compliance Workbook

The Update Compliance Workbook, relies on the data of the Update Compliance solution in the Azure subscription. That also means that the configuration of that Update Compliance solution is a prerequisite for the usage of the Update Compliance Workbook. To take advantage of the Update Compliance solution, make sure that the following configurations are in place.

Besides relying on the Update Compliance solution, to use the Update Compliance Workbook in the Microsoft Endpoint Manager admin center portal, it’s required to configure the Diagnostic settings (Reports > Diagnostic settings). That configuration will enable the IT administrator to add the Update Compliance Workbook and pin the different queries (visualizations and tables) to the dashboard.

Important starting point for the Update Compliance Workbook

Before using the Update Compliance Workbook, there are a few things to keep in mind.

  • Not every still serviced Windows 10 version has the same KB-number and the same revision number. That means that, for the best overview, a list of currently available security updates is required. Once those KB-numbers and revision numbers are all the same, the list can become a single KB-number.
  • Every Windows 10 device reports their own update status. That means that devices might report as up-to-date, while in fact those devices just didn’t report an updated status for the latest update.
  • A Windows 10 device can get to the correct Windows build with a clean install, by installing the latest feature update and by installing the latest security update. That might cause devices to report as up-to-date, while there is no information about the latest security update (behavior noticed during testing with 21H1).
  • Devices that are running a Windows Insider Preview build, won’t provide information about the latest update status. That means that there will be a difference between the number of devices that are showing per build and version and the number of devices showing per update type.

Update June 22, 2021: There was an issue in Update Compliance that caused update deployment status information of Windows 10 version 21H1 devices to be unavailable. That has been fixed by Microsoft.

Details about the visualizations in the Update Compliance Workbook

When looking in more detail at the Update Compliance Workbook, it basically contains a few charts and tables to visualize the Update Compliance information. Those charts and tables are provided in three different sections. A section with general version information, a section with update status information and a section with deployment status information. Besides those visualization, the Update Compliance Workbook is created with a few important automatically queried parameters.

Parameters

The parameters are a hidden section within the Update Compliance Workbook and are basically the heart and soul of the information that is shown. Those parameters provide easy access to a few important pieces of information. The first parameter contains the release date of the latest security update. That information is retrieved and set via the kusto query shown below.

WaaSDeploymentStatus
| where UpdateClassification == "Security"
| summarize max(UpdateReleasedDate)

The second parameter contains the latest security update. Well, actually, the latest security updates. The actual KB-numbers and revision numbers still differs for some versions of Windows 10, which results in a list of security updates. To retrieve the latest security updates, the release date of the latest security update is used. That information is retrieved and set via the kusto query shown below.

WaaSDeploymentStatus
| where UpdateClassification == "Security" and UpdateReleasedDate == "{LatestSecurityUpdatesReleasedDate}"
| summarize arg_max(TimeGenerated, *) by ReleaseName
| sort by ReleaseName asc
| summarize LatestSecurityUpdatesList = make_list(ReleaseName)
| project LatestSecurityUpdatesList

The third parameter contains a readable release date of the latest security update. That information is created and set via the kusto query shown below .

let months = dynamic({"1":"January","2":"February","3":"March","4":"April","5":"May","6":"June","7":"July","8":"August","9":"Sepetember","10":"October","11":"November","12":"December"}); 
let latestdate = datetime({LatestSecurityUpdatesReleasedDate});
print strcat(months[tostring(monthofyear(latestdate))]," ",dayofmonth(latestdate),", ",getyear(latestdate))

The fourth parameter contains the release date of the latest feature update. That information is retrieved and set via the kusto query shown below.

WaaSDeploymentStatus
| where UpdateClassification == "Upgrade"
| summarize max(UpdateReleasedDate)

The fifth parameter contains the latest feature update. To retrieve the latest feature update, the release date of the latest feature update is used. That information is retrieved and set via the kusto query shown below.

WaaSDeploymentStatus
| where UpdateClassification == "Upgrade" and UpdateReleasedDate == "{LatestFeatureUpdateReleasedDate}"
| summarize arg_max(TimeGenerated, *) by ReleaseName
| project ReleaseName

The sixth parameter contains a readable release date of the latest feature update. That information is created and set via the kusto query shown below.

let months = dynamic({"1":"January","2":"February","3":"March","4":"April","5":"May","6":"June","7":"July","8":"August","9":"Sepetember","10":"October","11":"November","12":"December"}); 
let latestdate = datetime({LatestFeatureUpdateReleasedDate});
print strcat(months[tostring(monthofyear(latestdate))]," ",dayofmonth(latestdate),", ",getyear(latestdate))

Section 1 – Number of devices per version and per build

The first section provides an overview of the number of Windows 10 devices per version and per build. That provides an overview, as shown below in Figure 1, with two pie charts for displaying that information. Simple but effective. Those charts help with providing an initial overview of the Windows 10 builds that are used within an organization. The first chart, that is shown below in Figure 1 on the left, is created by adding the information about the Preview Builds and counting the Windows 10 devices per Windows 10 version. That results in the kusto query as shown below.

WaaSUpdateStatus
| union WaaSInsiderStatus
| summarize arg_max(TimeGenerated, *) by ComputerID 
| summarize dcount(ComputerID) by OSVersion
| render piechart

The second chart, that is shown below in Figure 1 on the right, is created by adding the information about the Preview Builds and counting the Windows 10 devices per Windows 10 build. That results in the kusto query as shown below.

WaaSUpdateStatus
| union WaaSInsiderStatus
| summarize arg_max(TimeGenerated, *) by ComputerID 
| summarize dcount(ComputerID) by OSBuild
| render piechart

Section 2 – Feature and security update status per version and per device

The second section provides an overview of the status information of the latest feature update and the latest security update. That information is split in four separate rows with information. The first row provides information, as shown below in Figure 2, about the latest updates. The informational text on the left provides the latest available feature update and the release date of that feature update. That results in the markdown as shown below.

**Latest available Windows 10 feature update:** {LatestFeatureUpdate}  
**Release date:** {ReadableLatestFeatureUpdateReleasedDate}

The informational text on the right provides the latest available security update and the release date of that security update. That results in the markdown as shown below.

**Latest available Windows 10 security updates list:** {LatestSecurityUpdates}  
**Release date:** {ReadableLatestSecurityUpdatesReleasedDate}

The second row provides an overview of the number of Windows 10 devices per feature update status and per security update status. That provides an overview, as shown below in Figure 3, with two pie charts for displaying that information. Those charts help with providing an initial overview of the Windows 10 update status within an organization. The first chart – shown below in Figure 3 on the left and named Latest feature update status – is created by counting the Windows 10 devices per feature update status. That results in the kusto query as shown below.

WaaSUpdateStatus
| summarize arg_max(TimeGenerated, *) by ComputerID
| summarize dcount(ComputerID) by OSFeatureUpdateStatus
| render piechart

The second chart – shown below in Figure 3 on the right and named Latest security update status – is created by counting the Windows 10 devices per security update status. That results in the kusto query as shown below.

WaaSUpdateStatus
| summarize arg_max(TimeGenerated, *) by ComputerID
| summarize dcount(ComputerID) by OSSecurityUpdateStatus
| render piechart

The third row provides an overview of the number of Windows 10 devices per version per feature update status and per security update status. That provides and overview, as shown below in Figure 4, with two tables for displaying that information. Those tables help with providing an overview of the latest update status per version of the Windows 10 devices within an organization. The first table – shown below in Figure 4 on the left and named Latest feature update information per version – is created by counting the Windows 10 devices per version, branch and feature update status. That results in the kusto query as shown below.

WaaSUpdateStatus
| summarize arg_max(TimeGenerated, *) by ComputerID
| summarize Devices = count(ComputerID) by OSVersion, OSServicingBranch, OSFeatureUpdateStatus
| order by OSVersion asc, OSServicingBranch, OSFeatureUpdateStatus
| project Branch=OSServicingBranch, Version=OSVersion, Status=OSFeatureUpdateStatus, Devices

The second table – shown below in Figure 4 on the right and named Latest security update information per version – is created by counting the Windows 10 devices per version, branch and security update status. That results in the kusto query as shown below.

WaaSUpdateStatus
| summarize arg_max(TimeGenerated, *) by ComputerID
| summarize Devices = count(ComputerID) by OSVersion, OSServicingBranch, OSSecurityUpdateStatus
| order by OSVersion asc, OSServicingBranch, OSSecurityUpdateStatus
| project Branch=OSServicingBranch, Version=OSVersion, Status=OSSecurityUpdateStatus, Devices

The fourth row – hidden by default and only shows when selecting a line in the tables of the third row – provides an overview of the Windows 10 devices with the branch, version and update status of the selected line in the tables of the third row. That provides and overview, as shown below in Figure 5, with two tables for displaying information. Those tables help with providing an overview of the Windows 10 devices with the selected version and update status. Clicking on the device provides the remaining details of the device. The first table – shown below in Figure 5 on the left and named Device information for the selected Windows version and the status – is created by retrieving the details of the Windows 10 devices with the selected branch, version and feature update status. That results in the kusto query as shown below.

WaaSUpdateStatus
| summarize arg_max(TimeGenerated, *) by ComputerID
| where OSServicingBranch == '{Branch1}' and OSVersion == '{Version1}' and OSFeatureUpdateStatus == '{Status1}'
| order by Computer asc
| project Device=Computer, Branch=OSServicingBranch, Version=OSVersion, Build=OSBuild, Revision=OSRevisionNumber, Architecture=OSArchitecture, Edition=OSEdition, Deferral=iif(FeatureDeferralDays < 0, "Not set", strcat(FeatureDeferralDays," days")), Pause=FeaturePauseState, Scan=LastScan, TimeGenerated

The second table – shown below in Figure 5 on the right and named Device information for the selected Windows version and the status – is created by retrieving the details of the Windows 10 devices with the selected branch, version and security update status. That results in the kusto query as shown below.

WaaSUpdateStatus
| summarize arg_max(TimeGenerated, *) by ComputerID
| where OSServicingBranch == '{Branch2}' and OSVersion == '{Version2}' and OSSecurityUpdateStatus == '{Status2}'
| order by Computer asc
| project Device=Computer, Branch=OSServicingBranch, Version=OSVersion, Build=OSBuild, Revision=OSRevisionNumber, Architecture=OSArchitecture, Edition=OSEdition, Deferral=iif(QualityDeferralDays < 0, "Not set", strcat(QualityDeferralDays," days")), Pause=QualityPauseState, Scan=LastScan, TimeGenerated

Note: When only one of these tables is shown, that table will be positioned on the left. When both tables are shown, those tables are positioned below the selected information.

Section 3 – Latest security update deployment status per device

The third section provides an overview of the deployment status information of the latest security update per Windows 10 device. That provides an overview, as shown below in Figure 6, with a table displaying that information. That table is named Deployment status information of the latest available security updates per device and helps with providing an overview of the deployment status of the latest security update per Windows 10 device in the organization. Extra details can be shown by clicking on the deployment status. That results in the kusto query as shown below.

let SecurityUpdates = dynamic({LatestSecurityUpdates});
WaaSDeploymentStatus
| where ReleaseName in (SecurityUpdates)
| summarize arg_max(TimeGenerated, *) by ComputerID
| order by Computer asc
| project Device=Computer, Branch=OSServicingBranch, Version=OSVersion, Build=OSBuild, Release=ReleaseName, Deferral=iif(DeferralDays < 0, "Not set", strcat(DeferralDays," days")), DeploymentStatus, DeploymentError, DeploymentErrorCode, DetailedStatus, DetailedStatusLevel, PauseState, RecommendedAction, LastScan, TimeGenerated

Configuration of the Update Compliance Workbook

The configuration of the Update Compliance Workbook requires a few manual steps as described below. Those steps are focused on adding the Update Compliance Workbook in Microsoft Endpoint Manager admin center to enable the integration with the dashboard.

  • Download the Update Compliance Workbook here and open it in an editor
  • Open the Microsoft Endpoint Manager admin center portal and navigate to Reports > Workbooks
  • Click New and click Advanced Editor (</>)
  • Copy the content of the downloaded and opened .workbook file, paste the content and click Apply
  • With the different components (including the parameters) that are created click Edit and make sure that the Query section is pointing to the right Log Analytics workspace
  • Once all the errors are resolved, click Save

Note: It’s also possible to just add the Update Compliance Workbook to any other Log Analytics workspace with the Azure portal. That would, however, prevent the IT administrator from pinning queries to the dashboard in the Microsoft Endpoint Manager admin center portal.

Suggestions for enhancements of the Update Compliance Workbook

The Update Compliance Workbook is currently build on simplicity, to create awareness for the Update Compliance solution and to show how relatively easy it is to create a Workbook. A lot more is possible by created tabs and by showing more details. Suggestions are always welcome. A few potential enhancements are described below and are shown below in Figure 7. Those enhancements are based on providing a more realistic update status, by introducing an In deferral period status. So, that would make a device Up-to-date, Not up-to-date or In deferral period. That period is calculated by combining the release date of the update and the deferral period of the update. The first table – shown below in Figure 4 on the left and named Latest feature update information per version – is created by counting the Windows 10 devices per version, branch and feature update status. That results in the kusto query as shown below. The first example – shown below in Figure 7 on the left and named Latest security update information per version (including deferral period) – is created by counting the Windows 10 devices per version by update status. That results in the kusto query as shown below.

WaaSUpdateStatus
| extend InDeferralPeriod=iff(datetime_add('Day',QualityDeferralDays,datetime({LatestSecurityUpdatesReleasedDate}))>=now(),"Yes","No")
| extend TrueUpdateStatus=case(OSSecurityUpdateStatus == "Up-to-date", "Up-to-date", OSSecurityUpdateStatus == "Not Up-to-date" and InDeferralPeriod == "No", "Not Up-to-date", OSSecurityUpdateStatus == "Not Up-to-date" and InDeferralPeriod == "Yes", "In deferral period", "Not Up-to-date")
| summarize arg_max(TimeGenerated, *) by ComputerID
| summarize totalDevices = dcount(ComputerID),
    uptodateDevices = dcountif(ComputerID, TrueUpdateStatus == "Up-to-date"),
    indeferralperiodDevices = dcountif(ComputerID, TrueUpdateStatus == "In deferral period"),
    notupdateDevices = dcountif(ComputerID, TrueUpdateStatus == "Not Up-to-date") by OSVersion, OSServicingBranch
| project OSServicingBranch, OSVersion, totalDevices, uptodateDevices, notupdateDevices, indeferralperiodDevices

The second example – shown below in Figure 7 on the left and named Latest security update information per version (including deferral period) – is created by counting the Windows 10 devices by update status. That results in the kusto query as shown below.

WaaSUpdateStatus
| summarize arg_max(TimeGenerated, *) by ComputerID
| extend InDeferralPeriod=iff(datetime_add('Day',QualityDeferralDays,datetime({LatestSecurityUpdatesReleasedDate}))>=now(),"Yes","No")
| extend TrueUpdateStatus=case(OSSecurityUpdateStatus == "Up-to-date", "Up-to-date", OSSecurityUpdateStatus == "Not Up-to-date" and InDeferralPeriod == "No", "Not up-to-date", OSSecurityUpdateStatus == "Not Up-to-date" and InDeferralPeriod == "Yes", "In deferral period", "Not up-to-date")
| summarize dcount(ComputerID) by TrueUpdateStatus
| render piechart

More information

For more information about Update Compliance and the different schemas, refer to the following docs.

20 thoughts on “Enhance Update Compliance with a custom Workbook in Microsoft Endpoint Manager admin center”

  1. Great blog. Quick question, when I import the .workbook file and try to hit apply (well actually “Done Editing” as I can’t see Apply) I get the following error and it does not save:

    Cannot load from JSON: This item type is 3 (query), but the JSON provided was undefined (Unknown)

    Any idea? Thanks again for this as it looks very great.

    Reply
    • Hi Skender,

      That sounds like you went to the Advanced Editor of the initial query of a new Workbook. You need to go a bit higher on the screen to the Workbook controls and go to the Advanced Editor. That’s simply displayed as “” and is located between the pin icon and the smiley icon.

      Regards, Peter

      Reply
  2. Great article. It tried your improvement queries, but the following doe not give same result as shown in figure 7: Latest security update information per version (including deferral period)
    I assume a render condition is missing?

    Reply
    • Hi Christoph,
      It’s not a render condition that is missing, it’s a workbook column rederer configuration that is “missing”. To get the same result, you would need to configure the total column to be a composite bar and use the different counts as the columns within the composite bar.
      Regards, Peter

      Reply
  3. Hi Peter, thanks all works perfect no. One typo I found in kusto query related to figure 7. “Not Up-to-date” is case sensitive and you used different writings

    Reply
  4. Hi Peter,

    Great blog!
    one thing i noticed is that the “latest feature update” is stuck at 20h2, and not 21h1.
    it seems the latest release name is 20h1, not sure if you noticed the same.

    Regards,
    Ferry

    Reply
    • Hi Ferry,

      Sadly that’s “expected” behavior at this moment. Devices with 21H1 don’t seem to properly report information to WaaSDeploymentStatus. At this moment that also means that you won’t see any deployment status of security updates either, because of that. Hope that will be fixed soon…

      Regards, Peter

      Reply
  5. This works great! The only thing I’ve noticed is that I have 1621 not up-to-date 20H2 devices in the “Latest Security update status” graph but “latest feature update status” still shows 2400. any idea why that is?

    Reply
    • Hi Herman,

      The Latest feature update status is based on 21H1. So, every device not running 21H1 will be shown as not up-to-date. The Latest security update status is based on the actual latest security update. It’s very well possible that a device is not running the latest feature update, but is running the latest security update.

      Regards, Peter

      Reply
  6. Hoping you can help — when I paste this in, using the correct advanced editor option, I get an error with “”.

    The error is “SyntaxError: Unexpected token < in JSON at position 0".

    I am not a JSON guy, so I am at a complete loss on this, any ideas?

    Reply
  7. Hi Peter,

    First of all, congratulations about this article! One question. We trying to give access read only, but do not have sucessfull. What kind permission do we need to give?

    Thanks!

    Reply
  8. Hi,
    Firstly thank you for this, it’s great and my first foray into Log Analytics Workbooks. Due to our deferral period being quite long for quality updates (3 weeks), our devices are showing non-compliant against the latest releases. Is it possible to add a selection like “current compliance” and “last month’s” or something, so that we can see which devices received the last months patches and which didnt?
    Thank you,
    Luke

    Reply

Leave a Reply to Jon Miller Cancel reply

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