SCCM Software Compliance Report Filtered by Update Classification and Update Age

This SSRS Report has been sitting in the side lines for a while now. After spending a bit more time on it over the weekend I feel it is now at a point where it is serving me rather than the other way around.

Our teams wanted to know how many updates were outstanding for each client, how old the updates were and what the Update Classification was.

The real show stopper was trying to get Update Classification into the report. After finding the correct SQL view things started to fall into place.

Gives the following results:-

16777242 Applications
16777243 Critical Updates
16777244 Definition Updates
16777245 Drivers
16777246 Feature Packs
16777247 Security Updates
16777248 Service Packs
16777249 Tools
16777250 Update Rollups
16777251 Updates
16777252 WSUS Infrastructure Updates
16777466 Upgrades

So we could take this knowledge, and use CategoryInstanceID to filter Update Classifications – cool.

Cutting a long story short, and just putting this post together quickly because i promised to share the query, here it is in SQL Query format.

Modify @COLLID to the collection you want to test against. @COLLID is a parameter in our SSRS Report. We are only setting the variable here so we can return some results in SQL

Set @COLLID = ‘SMS00001’

Modify @COLLID value in your SQL Query

The RDL for SSRS is below for you your convenience. Remember to update the DataSource and modify the @COLLID Parameter to modify the Collections available to the report.

Download Here 
Compliance 12 – Patches Required for Collection_151218_Public.rdl

Had help from a few folks on this along the way:-

Leave a comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.


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

9 thoughts on “SCCM Software Compliance Report Filtered by Update Classification and Update Age”