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

*RDL updated to remove invalid link to custom Drill through report. Please remember to select your own data source after uploading the RDL to your Report Server

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

19 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
    ,col.SiteID
    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?

  4. I know this is an older post, but i am just starting with updates and think this can help. When i am selecting a host name i am getting an error: The item ‘/ConfigMgr_KC1/Software Updates – A Compliance/Compliance 5a – Specific computer (With Update Revised Date)’ cannot be found. (rsItemNotFound)
    I see you have responded to someone with “Choose the built in report Compliance Report 5” but i dont know where i need to make that change.
    Thank you.

      1. Hi Mike,

        Sorry, I see the issue now. As well as changing the DataSource to point to your CM Database, the “Drill through” link on the “Server name” also needs changing. The embedded link points to another custom report I have. Try the following:-

        1. Open RDL in Report Builder
        2. Right Click the Server <> Field and choose “Placeholder Properties”
        3. Choose Action > Go to report and click “Browse”
        5. Choose “Compliance 5 – Specific computer”
        6. Choose “Open”
        7. Press “OK”

  5. Hi Ben,
    First of all I wanted to thank you for sharing this report.
    This report works fine, however I have the same error as Mike (The item ‘/ConfigMgr_KC1/Software Updates – A Compliance/Compliance 5a – Specific computer (With Update Revised Date)’ cannot be found. (rsItemNotFound) when I click on a computer name for further patch details.
    I already changed the data source using report builder to point to my own site database to make the report work. I looked at Eswar’s blog and I have already done those steps. Any help will be much appreciate.

    1. Hi Jari,

      Sorry, I see the issue now. As well as changing the DataSource to point to your CM Database, the “Drill through” link on the “Server name” also needs changing. The embedded link points to another custom report I have. Try the following:-

      1. Open RDL in Report Builder
      2. Right Click the Server <> Field and choose “Placeholder Properties”
      3. Choose Action > Go to report and click “Browse”
      5. Choose “Compliance 5 – Specific computer”
      6. Choose “Open”
      7. Press “OK”

  6. Hi Khaled,

    You need to also edit the text box properties around the expression with the same action go to url to the location of compliance 5 report.
    Thanks Ben!! works brilliantly!

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.