By Karim Amri
Sr. SQL Server DBA at Exitas.
Since the default WSUS report only shows your system status against all available updates (except those that have been declined), your view of outdated systems may not be as you would like it to be.
For example, many companies only approve updates on a weekly, monthly or even quarterly basis to schedule the patch installations and this makes it hard to get an accurate view of the datacenter patch status against the patches that have been approved by the company.
Here is a query which generates a list of your out-of-date systems against the approved WSUS patches. The query is fast and only takes a second to execute in the SUSDB database with thousands of registered servers. The queries and reports I found online didn’t do what I want them to do and most ran more than 30 minutes to provide an output on our database.
This query shows the patches of all systems, if you only want to see systems that are not compliant instead of all systems in the database, remove the comment before “AND (PUBLIC_VIEWS.vUpdateInstallationInfoBasic.State IN (2, 3, 5, 6))”
I also created some additional code to mail the compliance report individually for each targetgroup you have defined in WSUS (for example to create reports for different company’s you have defined as targetgroups) Please mail me if you would like the code to do this
This blogpost was made because there are many people requesting a working wsus report against approved patches on the web, but no appropriate solutions were found. So here it is, once and for all.