This morning I saw an interesting tweet from Sandy Zeng with a Log Analytics workbook she’d created for W10 feature updates based on Update Compliance data. I’d been meaning to create a similar report for that myself in PowerBI for some time, so I took inspiration from her tweet and got to work on something!
Microsoft’s Update Compliance solution can be used to report on software updates and feature updates status across your estate from Windows telemetry data. If you use Desktop Analytics, you can even combine the data for richer reporting.
Log Analytics allows exporting of queries in Power Query M formula language, which can be imported into PowerBI to create some nice reports.
Here’s a screenshot of what I ended up with. You can filter the data to view devices with Safeguard holds, for example, which since Windows 10 2004 has been a show stopper for many wanting to upgrade…
There are pages for 2004 and 20H2, as well as a page listing some of the known Safeguard hold IDs that have been publicly disclosed by Microsoft.
You can download this report for your own use with the links below. Note I have created two reports – the first assumes you have both Update Compliance and Desktop Analytics using the same Log Analytics workspace. If this is not the case for you, download the second report which doesn’t link to DA data and just uses Update Compliance. The only data I’ve included from DA is the make and model since these can be helpful in analysing devices affected by Safeguard holds.
To create your own report, you’ll need the latest version of PowerBI desktop installed, with preview support for dynamic M query parameters.
Open PowerBI and go to File > Options and settings > Options > Preview features and enable Dynamic M Query Parameters.
Restart PowerBI and open the downloaded PBI template. Upon opening, you’ll be prompted for the Log Analytics workspace ID. You can find this on the Overview pane of your workspace in the Azure portal.
The reports contain data with a timespan of the last 48 hours, but you can change this if you want by editing the queries in the Advanced editor, and changing the value “P2D”.
You also might want to play around with the LastScan filter so you only get devices with a recent scan date, and avoid duplicates.
Hope it’s helpful!