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_121120_Public.rdl

*UPDATE* 12/11/2020 – 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
You will also need to edit the RDL with a text editor and replace ConfigMgr_SS1 with ConfigMgr_XXX where XXX is your Site Code

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

4.5/5 - (2 votes)

36 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!

  7. Hi Ben,

    I try update the sql with other device collection, this collection have 1000 client. When I run the report, it just show only 7xx client. Can you guy me which sql statement have limit\filter the client

    Thanks

  8. Hi Ben,

    I have update the collection to others which have ~1000 client. When I run the report, it just only show 7xx client. Can you guide me which sql statement had limit\filter the client.

    Thanks

  9. I have tried all the steps and still get this when I click on a computer name for further patch details. (The item ‘/ConfigMgr_KC1/Software Updates – A Compliance/Compliance 5a – Specific computer (With Update Revised Date)’ cannot be found. (rsItemNotFound). Can you please assist, step by step with photos if possible

    1. As Shayan posted

      “ 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.”

      I’ll re-upload the RDL later without the link to the custom report as it can be confusing

      1. That will be appreciated. I did as you said but still errored out as above. I must be missing something. Maybe the old report as is and it can point me to where the /ConfigMgr_KC1/Software Updates – A Compliance/Compliance 5a – Specific computer is.
        Thanks

        1. Uploading modified RDL now – You need to open the RDL with a text editor and replace ConfigMgr_SS1 with ConfigMgr_XXX where XXX is your Site Code. Then upload the RDL to your reports server.

  10. Thanks you so much Ben! its working now. In addition to the site code change I had to ensure the path is Software Updates – A Compliance/Compliance 5a – Specific computer, removing the other stuff after.
    You are Chingon! as the say in spanish

  11. How can we add or prompt collection name to while running the report? I don’t want to edit the report every time for different collections.

  12. Hello,
    Thanks for the great report!
    I noticed that some of my WIndows Serevr 2016 (v1607) and some Windows 10 are getting the same results:
    0 to 30: 11
    31 to 60: 4
    61 to 80: 9
    81 plus: 60
    But the clients are updated till October 2020.
    Is there any issue with these clients?
    Thanks in advanced!

    1. Hi Sergio, the click through report will indicate which updates are required by your clients. It might be that some updates have not been “approved” but are “required” by your clients.

      1. Just found the issue. Nothing to do with your Report.
        I Have some VDI Servers with Office installed and SCCM was not deploying the Updates to them (the query on the Collection was only for clients). So, the results from the Report were from Office Updates.
        Thanks!

  13. Just found the issue. Nothing to do with your Report.
    I Have some VDI Servers with Office installed and SCCM was not deploying the Updates to them (the query on the Collection was only for clients). So, the results from the Report were from Office Updates.
    Thanks!

  14. Just found the issue. Nothing to do with your Report.
    I Have some VDI Servers with Office installed and SCCM was not deploying the Updates to them (the query on the Collection was only for clients). So, the results from the Report were from Office Updates.
    Thanks!

Leave a Reply to Shayan Cancel Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

 

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