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:-

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

  1. Which database rights does the account that runs the script need on the database? When running the script it gives me this error

    The SELECT permission was denied on the object ‘BGB_LiveData’, database ‘ConfigurationManager_P01’, schema ‘dbo’.
    Query execution failed for dataset ‘DataSet1’. (rsErrorExecutingCommand)
    An error has occurred during report processing. (rsProcessingAborted)

    1. Hi Marcel, I have update the RDL now so the Select statement runs against a view instead of the live SQL table. Try downloading it again or update that part of the query: Replace “left join BGB_LiveData bl on bl.resourceid=ucs.resourceid” with “left join v_CombinedDeviceResources bl on bl.MachineID=ucs.resourceid”

      1. I noticed… ๐Ÿ™‚

        The item ‘/ConfigMgr_PO1/Software Updates – A Compliance/Compliance 5a – Specific computer (With Update Revised Date)’ cannot be found. (rsItemNotFound)

          1. In our environment your report tells 8 updates are required and it tells what classifications, the linked report however (number 5) gives a whole load of patches and we have to manually find out what the 8 required patches are. Would it be possible to adjust or create a report so only the 8 required show in the report?

          2. You can copy and then modify the inbuilt report “Compliance 5” and add this to the “Where” clause on line 60

            and (cls.CategoryInstanceName='Critical Updates' or cls.CategoryInstanceName = 'Security Updates' or cls.CategoryInstanceName = 'Service Packs' or cls.CategoryInstanceName = 'Update Rollups')

  2. Hello fellow Ben.

    Thanks for this report! It built on a report I was using and made it much more useful.

    Something that helps me with assigning collections to RDL’s is setting up a query for the Parameter @COLLID. In that query I look for specific text in the Comment field of all of my collections, so that I can add or remove collections without editing my reports. Below is the query for @COLLID. Set the Value Field for SiteID and the Label Field for CollectionName.

    Select distinct col.CollectionName
    From v_FullCollectionMembership fcm
    JOIN Collections col on col.SiteID = fcm.CollectionID
    WHERE CollectionComment LIKE ‘%_Compliance_rpt%’
    Order By col.CollectionName

  3. If I wanted to exclude “Patch my PC” as a vendor from the totals, how would I modify this query to work?

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.