UPDATE: I’ve released a larger set of reports and included a new version of this report which is more interactive and attempts to handle the Windows N versions. If you are running those in your environments please reach out in the comments below to let me know if it works or not.
Forgive me SQL gods for I have sinned and fallen astray. I have done wrong and written queries that I am not proud of; the mere sight of which cause children to cry.
Way back when WannaCry came out and became a thing (remember that? Oh the fond memories) I wasn’t particularly worried. The patches had been released a couple months earlier and we had moved to the cumulative update rollups. So in theory we had already patched those vulnerabilities not once but twice and were on the verge of doing it yet a third time. We were protected and we had the compliance reports to prove it.
Except we weren’t. You see … the cumulative updates have prerequisites. And the funny thing about a missing prerequisite is that the devices will report the update as not applicable rather than not installed. Since for any given update the vast majority of devices are not applicable it’s easy to miss and your reports will show that all ‘applicable’ patches are installed. What got us was a beauty: MS decided to release updates with circular dependencies. If you want the details, read my Reddit thread here. With the recent Meltdown/Spectre updates requiring a registry value as a prerequisite it’s again a very timely problem.
That experience wasn’t fun and made me realize the need for a report focused on just the cumulative updates based on their targeted OS. The report I’m releasing here does … mostly … that.
It’s worth pointing out that I don’t consider this report some silver bullet. This shouldn’t be the only compliance report you use. In fact, I plan to release the small interactive patch dashboard I created that will work together with this report. However, when security wants to know if you’re protected from X based on cumulative patch Y this should tell you quite accurately.
What Is this Monstrosity Doing?
The main goal of this report is to determine what OS each device is running, what OS each cumulative update applies to, marry the two, and report compliance. The end result is that anything reporting as not applicable is as bad as not installed. In the end this report doesn’t report not applicable because of the views used but the point remains: any device not showing ‘update installed’ is bad.
I didn’t get all the way down to a one to one relationship between device and update because Windows 10 and Server 2016 have multiple supported versions. I didn’t want to hard code a cross reference based on build number that would be quickly outdated. Instead the report gets most of the way there and then looks to see if any update for that OS is installed, required, not applicable, or unknown in that order. If the state is unknown then the report is going to list a semi-random update that might not technically be applicable but still signals that the device does not have the latest cumulative update installed.
To determine what the latest cumulative update is the report runs some custom code (SSRS can do that!) to calculate the last Patch Tuesday and creates the period (ex. 2018-01) based on that. You can always modify that parameter if you’re a month behind for some reason.
In any case, enjoy the report and let me know what I got wrong.
Hi, i have downloaded the latest Version (1.5) via GibHub and imported it. I noticed that for all Windows Server 2022 under Updates “Cumulative Update not found” and State Name “Detection state unknown” is displayed. Maybe something needs to be adjusted here?
Same to me .
Same but for the whole report regardless of OS. The only thing the report is pulling for me is the Computer Name in the first column.
Looking for theCompliance Report For Latest Cumulative Updates
What permissions are needed on the database to run this successfully?
I got this error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘DataSetAdminID’. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
And, I currently have ‘read’ permissions only on my SCCM database server.