Enhance Update Compliance Reporting with Azure Automation

In my last blog I provided an Intune Proactive remediations script that can be used to gather additional Windows Update information from your managed Windows devices and send the data to an Azure Log Analytics workspace to enhance your Windows Update reporting together with Microsoft’s Update Compliance solution.

In this blog, I will provide an Azure Automation Runbook that can be used to add even more Windows Update data to your LA workspace for Windows 10 and 11 devices. This is an abridged version of the runbook I use with my own Update Compliance solution and retrieves data from MS docs on:

  • Windows update history (ie history of security and non-security cumulative updates per Windows release)
  • Windows update support info (ie whether a Windows version and edition is currently in support and how long for)
  • Latest Windows updates (ie what are the most recent security and non-security updates per Windows release including n-1 and n-2 updates)
  • Documented Windows Update error codes and descriptions

The info is ‘scraped’ from MS docs pages, including:

After executing the runbook, you’ll have 4 new custom logs in your LA workspace:

SU_OSLatestUpdates_CL

This table contains each Windows version with the build number, KB, release date and update type of the latest security monthly ‘B’ update, the latest preview update and the latest out-of-band update (if applicable), including for n-1 and n-2 updates. ‘LatestUpdate’ here means the latest CU whether a ‘B’ or ‘C’ or OOB update, and ‘LatestRegularUpdate’ is specifically the monthly ‘B’ security update.

SU_OSSupportMatrix_CL

This table contains each Windows release with the release date, end of support date, support period, support days remaining, is it in support currently – per edition family, ie Home and Pro, or Enterprise and Education.

SU_OSUpdateHistory_CL

This table contains each cumulative update released for each Windows release including the KB number, build number, release date and update type.

SU_WUErrorCodes_CL

This table contains documented Windows Update error codes together with a category and friendly description.

Create the Azure Automation Runbook

I’ll assume you already have an Azure Automation account and Log Analytics workspace to work with. If you are already using Microsoft’s Update Compliance you can use the same Log Analytics workspace to avoid cross-workspace queries.

Create encrypted variables

In the Automation account, under Shared resources > Variables, create two encrypted variables called WorkspaceID and PrimaryKey. Copy the Workspace ID and the Primary Key of your Log Analytics workspace into the variables (get these from Settings > Agent management).

Create the Runbook

Create a new runbook with a super-original name and use the 5.1 runtime version.

In the Edit PowerShell Runbook window, paste in the contents of this script

No changes are required to the script, with the possible exception of line 695. This line controls how frequently the Windows Update error code information is pulled from MS docs. Because this info rarely changes, I only run it twice a month on days 7 and 21, just to prevent the data from ageing out of the workspace due to the data retention period. You may wish to change one of those values to today for the first run so you’ve got some data right away.

Save and Publish your runbook and then start it! The runbook only takes seconds to execute and after a successful job you should see http 200 status codes output for the posts to the LA workspace.

Assign a schedule to the runbook to keep the data up-to-date – I run mine every 4 hours because I also calculate device compliance with it, but otherwise a daily run might be sufficient.

Sample KQL queries

Once your data is available in the workspace, here are some example KQL queries you can use on the data.

Query the support info for Windows versions

SU_OSSupportMatrix_CL 
| summarize arg_max(TimeGenerated,*) by 
    Windows_Release=Windows_Release_s,
    Version=Version_s,
    EditionFamily=EditionFamily_s
| project 
    Windows_Release,
    Version,
    InSupport=InSupport_s,
    EditionFamily,
    StartDate=StartDate_s,
    EndDate=EndDate_s,
    SupportPeriodInDays=SupportPeriodInDays_d,
    SupportDaysRemaining=SupportDaysRemaining_d,
    TimeGenerated
| order by Windows_Release,Version,EditionFamily desc

Query the update history for Windows versions

SU_OSUpdateHistory_CL
| summarize arg_max(TimeGenerated,*) by Windows_Release_s,OSVersion_s,OSBuild_s
| project
    Windows_Release=Windows_Release_s,
    OSVersion=OSVersion_s,
    KB=KB_s,
    OSBaseBuild=OSBaseBuild_d,
    OSBuild=OSBuild_s,
    OSRevisionNumber=OSRevisionNumber_d,
    ReleaseDate=ReleaseDate_t,
    Type=case(Type_s == "Regular","Security 'B'",
        Type_s == "Preview","Non-Security Preview",
        Type_s),
    TimeGenerated
| order by Windows_Release desc,OSVersion desc,ReleaseDate desc

Query documented Windows update error codes and descriptions

SU_WUErrorCodes_CL
| summarize arg_max(TimeGenerated,*) by ErrorCode_s
| project
    ErrorCode=ErrorCode_s,
    Message,
    Description=Description_s,
    Category

Query the latest updates for each Windows version

SU_OSLatestUpdates_CL
| summarize arg_max(TimeGenerated,*) by Windows_Release_s,OSVersion_s
| project 
    TimeGenerated,
    Windows_Release=Windows_Release_s,
    OSBaseBuild=OSBaseBuild_d,
    OSVersion=OSVersion_s,
    LatestUpdate=LatestUpdate_s,
    LatestUpdateType=LatestUpdateType_s,
    LatestUpdateKB=LatestUpdate_KB_s,
    LatestUpdateReleaseDate=LatestUpdate_ReleaseDate_s,
    LatestRegularUpdate=LatestRegularUpdate_s,
    LatestRegularUpdateKB=LatestRegularUpdate_KB_s,
    LatestRegularUpdateReleaseDate=LatestRegularUpdate_ReleaseDate_s,
    LatestPreviewUpdate=LatestPreviewUpdate_s,
    LatestPreviewUpdateKB=LatestPreviewUpdate_KB_s,
    LatestPreviewUpdateReleaseDate=LatestPreviewUpdate_ReleaseDate_s,
    LatestOutofBandUpdate=LatestOutofBandUpdate_s,
    LatestOutofBandUpdateKB=LatestOutofBandUpdate_KB_s,
    LatestOutofBandUpdateReleaseDate=LatestOutofBandUpdate_ReleaseDate_s,
    LatestRegularUpdateLess1=LatestRegularUpdateLess1_s,
    LatestRegularUpdateLess1KB=LatestRegularUpdateLess1_KB_s,
    LatestRegularUpdateLess1ReleaseDate=LatestRegularUpdateLess1_ReleaseDate_s,
    LatestPreviewUpdateLess1=LatestPreviewUpdateLess1_s,
    LatestPreviewUpdateLess1KB=LatestPreviewUpdateLess1_KB_s,
    LatestPreviewUpdateLess1ReleaseDate=LatestPreviewUpdateLess1_ReleaseDate_s,
    LatestOutofBandUpdateLess1=LatestOutofBandUpdateLess1_s,
    LatestOutofBandUpdateLess1KB=LatestOutofBandUpdateLess1_KB_s,
    LatestOutofBandUpdateLess1ReleaseDate=LatestOutofBandUpdateLess1_ReleaseDate_s,
    LatestRegularUpdateLess2=LatestRegularUpdateLess2_s,
    LatestRegularUpdateLess2KB=LatestRegularUpdateLess2_KB_s,
    LatestRegularUpdateLess2ReleaseDate=LatestRegularUpdateLess2_ReleaseDate_s,
    LatestPreviewUpdateLess2=LatestPreviewUpdateLess2_s,
    LatestPreviewUpdateLess2KB=LatestPreviewUpdateLess2_KB_s,
    LatestPreviewUpdateLess2ReleaseDate=LatestPreviewUpdateLess2_ReleaseDate_s,
    LatestOutofBandUpdateLess2=LatestOutofBandUpdateLess2_s,
    LatestOutofBandUpdateLess2KB=LatestOutofBandUpdateLess2_KB_s,
    LatestOutofBandUpdateLess2ReleaseDate=LatestOutofBandUpdateLess2_ReleaseDate_s
| order by Windows_Release,OSVersion

Power BI

I’m a sucker for a nice Power BI report. Here’s a couple of examples of how you could report on this data:

Known Issues

Since MS docs update history does not contain updates for RTM releases, when a new feature update is released, there will be no update history reported until the release of the first CU.

A final note: Gary Blok has created a feedback item to make it easier to get Windows Release Information from Microsoft via an API. You can upvote it here: aka.ms/AAh5c1x

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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