SCCM Patch Compliance Reports

Updated: 2013-11-20 – Fixes for rdl files and 2012 report, I’ve edited the RDLs but I have no infrastructure currently to test on so feedback is welcome.

I was watching SCCM Guru Episode 9 with Garth Jones, when I saw in his powerpoint presentation two reports for patch compliance that I liked the look of. So I decided to see if I could emulate the reports, I will say here having only seen the final product in his slide my reports will look similar to his, and may even share code, however I have not seen ANY of his code so any similarities here are purely coincidental and not me just copying his code and taking credit.

Report 1: MissingUpdatesByCollection – This report will list the counts of missing updates for all systems in a collection where the updates are listed in the specified update list. This report is set to link to the MachineVulnerabilityReport to provide the ability to drill down. It is worth editing the collections query for this report as currently it is set to show all XP, all Servers and all 2003 servers, which depending on your site be a rather large amount of machines. You may also want to edit the report name field for the link if you rename these reports.

Report 2: MachineVulnerabilityReport – This report will list the missing updates for a specific system where the updates are listed in the specified update list.

As always if you know of any improvements that could be made to the reports or notice any bugs please leave a comment.


  • [download id=”48″]
  • – Now Includes Count error Fix, Thanks to Russ

  • [download id=”54″]
  • – Submitted by Max in Comments section, Untested by me if someone can confirm it works it would be appreciated.

  • [download id=”49″]

51 thoughts to “SCCM Patch Compliance Reports”

  1. Any possibility these reports will be updated to include Windows 10? I’ve tried to make some changes but have not be successful. Thank you. Peter

  2. Thanks for putting this together; it was exactly what I’ve been scouring the internet for.

    For anyone coming across issues with the 2012 vulnerability report, I just replaced all beginning and end quotes with single ‘ and issue resolved

  3. Sorry for the slow approval of the post I was on holiday.

    I *think* there may be a field in “INNER JOIN v_UpdateInfo ui” that you can use in the Where statement.

  4. Question, on your machine vulnerability report, you are only showing missing security updates. How would you change this query in the report builder to show ALL missing updates defined in the SUG?

  5. @Ben – I found an issue with the “NONE” severity that needed to be corrected.

    @ScottW – If devices are missing from the report it “should” be because they have no updates to install. No news is good news. The report is written to only show devices that HAVE updates to install. And remember this is dependent on a successful SUG update and successful client side scan against it.

  6. @ScottW

    If you have access to the DB and can run the queries manually (you can see them near the top of the RDL file).

    That may shine some light on why you are not seeing the Systems.

    I’ll try and answer where possible but I haven’t administrated ConfigMgr in ~4years, so I am quite rusty.


  7. I’ve been trying to get the “Missing Updates by Collection” report to work properly and I’m stumped. It runs but only returns some of the systems in a give collection. For instance, I’ve got 13 Windows 2003 servers in a collection and when I report on them I only get data for 6 of them. Same kind of results against the 334 servers in the Windows 2008 collection where I only get 201 servers in the report. It looks like this report is exactly what I’m looking for if I can get it to execute properly.


  8. @Roger,

    I would strongly advise against that sort of report. Such a report would not only be huge it would be unreadable and essentially useless as no-one would ever read it all.

    Assuming your collection contains a reasonably small number of servers 500 and you release 10 updates in a patch cycle to that collection. That means your report will consist of 5000 rows assuming 50 rows per page that will be a report of 100 pages. Either to print or click next on. Multiply that up by the number of collections you have assuming you have more than one.

    The way the data is presented in the above reports is much more efficient and you could easily tweak them to add charts/graphs that management can use to get an overview.

  9. Im completely new to SSRS but my boss is wanting a report that shows each machine in a collection and its status PER update (compliant/non-compliant) in a Update group. Is there anything out there that shows that? I can drill down and get all this but he wants a one stop shop where he can see all the details without actually clicking.

  10. Got one sug for each month of ms patches to make sure we have different deadlines and to keep track of where failed patches appear.

    Bit I still want a report on overall compliance and that means selecting all sugs for one collection

  11. @Peter L

    If I understand you correctly you cannot select multiple SUGs. I’m not sure why you would want to.

    However here are the queries I’m using that work for me:

    UpdatesList (Dataset)

    ORDER BY Title

    CollectionList (Dataset)

    CollectionID = ‘xxx’ — Your Desired Display Name
    Name Like ‘%blah%’
    ORDER by Name

  12. Hi Ryan, great post and love the report!

    As Im a bit new to this. Is there a way so I can select mutiple software update Groups and only one Collection?

    If i change the report to accept multiple values for the Update list the report failes with wrong syntax

  13. Got it. Thanks. Gary was right, if you look close enough at the single quotes in the query they are messed up.

  14. @ E$ and Yan,

    The file has probably got the quotes encoded in UTF or unicode, Try opening with notepad++ / notepad2 I think they will show the quotes as non printable characters.


  15. Hello, when I run the SU-MachineVulnerability2012 script I get error:

    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘ComputerInfo’. (rsErrorExecutingCommand)
    Incorrect syntax near ‘’’.

    Gary mentioned something about fixing quotes but I don’t see what he is referencing.

    Can you please help? Thanks!

  16. Hello,

    Can you please let me know what needs fixing with the quotes in ComputerInfo?


  17. Using with SCCM 2012 R2, both reports are working fine, thank you. I just had to edit the dataset query “ComputerInfo” with Notepad, do a “Find and Replace” and fix quote marks.

  18. @Sakkie,
    I assume it does run under 2012, noone has said it doesn’t, but I have no way of testing still.

    The reports were created in MS BI 2008, and the lists are part of the tools.
    iirc CollID is populated directly from SQL,
    UpdateBefore is just a date and if set then it is used to ensure you are not looking at too recent a list of updates.
    AuthListID again is just pulled from the database directly.


  19. Hi Guys, I am a newbie to SCCM 2012 reports. I downloaded the SU – MissingUpdatesByCollection report and imported all the datasets.

    My question is, the CollID, UpdateBefore and AuthListID how do you create the dropdown lists?


  20. You can disregard my prior comment. It was smartquotes causeing the issue. Now I have to figure out why the missing updates will not populate.

  21. I’m getting this error:
    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘ComputerInfo’. (rsErrorExecutingCommand)
    For more information about this error navigate to the report server on the local server machine, or enable remote errors
    Any Suggestions? Thanks, these are great reports, just what I was looking for.

  22. @Derrick,

    Sorry for the delay in response.

    Which report are you running? The 2012 version or the 2007 version?



  23. Hello,

    Thank you for the report when I try to run the report I am getting the following error:

    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘ComputerInfo’. (rsErrorExecutingCommand)
    Incorrect syntax near ‘.

    Do you know why?

  24. In order to fix the calculation issue, modify the UpdatesMissing Dataset and add a – in between Add and on, so it appears as follows:

    SUM(CASE WHEN Severity = ‘Add-on’ AND Age = 0 THEN 1 ELSE 0 END) AS LessAddon,
    SUM(CASE WHEN Severity = ‘Add-on’ AND Age = 1 THEN 1 ELSE 0 END)

    I also added a clickable URL column for each hotfix as well to mine, very nice report thank you!

  25. i just tried it,running but the total count of patches do not matches with the summary of crital,medium an low…there is differnce

  26. Hello Ryan,

    I meant to respond back earlier, shortly after I posted the comment I figured it out, Thank you!

    However, now I have them imported, I run the report/s choose the data I want, i.e. security patches, collection but it returns no data. I have successfully imported other RDL files so I know the data source is correct in these.

    -Paul M

  27. For those interested, I figured out all the correct tables/fields for the second Machine Vulnerability Report SQL query for SCCM 2012.

    SELECT vrs.Netbios_Name0 AS [Device Name], vrs.Client_Version0 AS [SCCM Client], sites.SMS_Assigned_Sites0 AS Site, cs.Domain0 AS Domain,
    cs.Manufacturer0 AS Manufacturer, cs.Model0 AS Model, cs.UserName0 AS [Console ID], os.Caption0 AS [Operating System],
    os.CSDVersion0 AS [Service Pack], os.LastBootUpTime0 AS [Last Boot], os.InstallDate0 AS [Build Date], uss.LastWUAVersion AS [WSUS Client],
    uss.LastScanTime AS [WSUS Scan], chcs.LastHW AS [Hardware Scan], chcs.LastSW AS [Software Scan], CASE WHEN (se.ChassisTypes0 = ‘1’)
    THEN ‘Other’ WHEN (se.ChassisTypes0 = ‘2’) THEN ‘Unknown’ WHEN (se.ChassisTypes0 = ‘3’) THEN ‘Desktop’ WHEN (se.ChassisTypes0 = ‘4’)
    THEN ‘Low Profile Desktop’ WHEN (se.ChassisTypes0 = ‘5’) THEN ‘Pizza Box’ WHEN (se.ChassisTypes0 = ‘6’)
    THEN ‘Mini Tower’ WHEN (se.ChassisTypes0 = ‘7’) THEN ‘Tower’ WHEN (se.ChassisTypes0 = ‘8’) THEN ‘Portable’ WHEN (se.ChassisTypes0 = ‘9’)
    THEN ‘Laptop’ WHEN (se.ChassisTypes0 = ’10’) THEN ‘Notebook’ WHEN (se.ChassisTypes0 = ’11’)
    THEN ‘Hand Held’ WHEN (se.ChassisTypes0 = ’12’) THEN ‘Docking Station’ WHEN (se.ChassisTypes0 = ’13’)
    THEN ‘All in One’ WHEN (se.ChassisTypes0 = ’14’) THEN ‘Sub Notebook’ WHEN (se.ChassisTypes0 = ’15’)
    THEN ‘Space-Saving’ WHEN (se.ChassisTypes0 = ’16’) THEN ‘Lunch Box’ WHEN (se.ChassisTypes0 = ’17’)
    THEN ‘Main System Chassis’ WHEN (se.ChassisTypes0 = ’18’) THEN ‘Expansion Chassis’ WHEN (se.ChassisTypes0 = ’19’)
    THEN ‘SubChassis’ WHEN (se.ChassisTypes0 = ’20’) THEN ‘Bus Expansion Chassis’ WHEN (se.ChassisTypes0 = ’21’)
    THEN ‘Peripheral Chassis’ WHEN (se.ChassisTypes0 = ’22’) THEN ‘Storage Chassis’ WHEN (se.ChassisTypes0 = ’23’)
    THEN ‘Rack Mount Chassis’ WHEN (se.ChassisTypes0 = ’24’) THEN ‘Sealed-Case PC’ ELSE se.ChassisTypes0 END AS [Chassis Type]
    FROM dbo.v_CH_ClientSummary AS chcs LEFT OUTER JOIN
    dbo.v_r_system AS vrs on chcs.ResourceID = vrs.ResourceID LEFT OUTER JOIN
    dbo.v_GS_COMPUTER_SYSTEM AS cs ON chcs.ResourceID = cs.ResourceID LEFT OUTER JOIN
    dbo.v_GS_OPERATING_SYSTEM AS os ON chcs.ResourceID = os.ResourceID LEFT OUTER JOIN
    dbo.v_GS_SYSTEM_ENCLOSURE AS se ON chcs.ResourceID = se.ResourceID LEFT OUTER JOIN
    dbo.v_RA_System_SMSAssignedSites as sites ON chcs.ResourceID = sites.ResourceID LEFT OUTER JOIN
    dbo.v_UpdateScanStatus AS uss ON chcs.ResourceID = uss.ResourceID
    WHERE chcs.ResourceID = @CompID

  28. If you are talking about in Production:
    Goto the Reporting Site, choose upload, once uploaded go the view where you can see the report listed and hover over it there should be a dropdown. Choose Manage and then datasources.
    To see which datasource to use manage one of the existing reports and see where they point to.


  29. Aaron,

    I’m afraid that I am only going to be of limited help as I have no access to a SCCM install. But can you load the report in VS 2005 and execute the query or if you look in the rdl file you should be able to see the query if you have access to the DB you can try and execute that from the SQL Management Studio. Might give you a better error.


  30. 1st off, great re-engineering! These reports are very useful for me. I was able to get the reports working after customizing the datasource (and adding the right DB to connect to). However the secondary report (MachineVulnerability) is failing with the error:

    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘ComputerInfo’. (rsErrorExecutingCommand)
    For more information about this error navigate to the report server on the local server machine, or enable remote errors”

    Ideas on what to correct in the second report?

  31. Thank you for the report. Got it mostly working and it looks like it will fill a void very nicely

  32. First of all, nice work on these reports. I’m getting an error “Query execution failed for dataset ‘ComputerInfo’. (rsErrorExecutingCommand)”. If I open the ‘ComputerInfo’ dataset and attempt to run the query I get the “define query parameters” box that contains the “@CompID” parameter name with a “” parameter value. What am I doing wrong?

  33. Datasource uses a unique GUID to id it. Once the reports are imported if you open them in SSRS they will complain about the datasource. If you then go to the properties of the report and the datasources tab you can can “browse” to the data source tat the rest of your SCCM reports use (assuming this is on the same box and not a separate central reporting site).

  34. Thank you. I am on teh track now I think. Imported the reports but got message about datasource. Looks like I need to create the underlying data source or Report Models.

    Thanks again

  35. It’s not HTML it’s XML. The txt you are seeing are the contents of the file in text, just right click the file and “save as” change the file extension from .txt to .rdl and upload to your SSRS Server. Note the changes in the comments below as I have not updated the files to reflect these errors yet.


  36. In trying to download these files all I get is an HTML page instead of a download. ANy ideas how I can download the files and the dataset that accompanies them?



  37. I think I just got it. I hit the ellipsis next to the “UpdatesMissing” dataset and found a “Parameters” tab that was empty. I entered the three names: @AuthListID, @CollID, and @UpdatesBefore. The values can be populated using the corresponding drop-down box. After saving this, the report worked! Hope this works for Ben. Thanks for the assistance and suggestions Ryan.

  38. Those are just the Values you would select from the drop-down at the top of the report in the SSRS webview. Have you tried opening the reports vairables menu in VS and ensuring they are present?

  39. The dataset runs but the report still shows “Query failed…must declare scalar variable @AuthListID and @CollID”. If I replace these variables with literal values in the dataset, the report runs successfully.

  40. @Ken

    Good catch those should be the same, change them both to ‘Add-on’. Have you tried re-running the dataset query on the dataset page before attempting to rerun the report. Also if you are running from within VS then if you go to the project folder there should be two files with the same name one with the extension ‘.rdl’ keep that one and delete the other and try running the report again.



  41. I love the report too — thanks for posting it. I’ve been trying to come up with something similiar for a long time but have been unsuccessful.

    The “Add-on” number is showing up in the total but not in the ‘Other Updates’ column. I noticed that the case ui.severity uses ‘Add-on’ but the temp table uses ‘Addon’.

    After making the values the same, the “UpdatesMissing” dataset displays correctly but the report throws an error (could not generate a list of fields for the query). I’m still trying to figure it out…

  42. @Ben,

    I will have to apologise as I am unable to help for at least the next few months. I actually changed jobs a couple of weeks ago so I don’t have access to any infrastructure to run the report against at the moment and due to a (hopefully) pending house move my lab is out of action as well.



  43. Thanks for the report – I love it! The bad news is that I imported it and ran it, but it’s not accurate. I don’t know why. After running, it initially looks correct, but the numbers in each column don’t add up to the Total. Also, I ran native reports against some of the same hostnames and results are different.

    I love the report and want it to work. What am I or it doing wrong? Thanks!

Leave a Reply

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


This blog is kept spam free by WP-SpamFree.