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
