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.

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

• Figure 1: An overview of the first section of the Update Compliance Workbook

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}

• Figure 2: An overview of the first row of the second section of the Update Compliance Workbook

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

• Figure 3: An overview of the second row of the second section of the Update Compliance Workbook

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

• Figure 4: An overview of the third row of the second section of the Update Compliance Workbook

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

• Figure 5: An overview of the fourth row of the second section of the Update Compliance Workbook

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, ReleaseName
| 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

• Figure 6: An overview of the third section of the Update Compliance Workbook

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

• Figure 7: An overview of potential enhancements of the Update Compliance Workbook

More information

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