SCCM Patch Compliance Reports
by Ryan on Apr.03, 2012, under Coding/Scripting, Reporting Services, SCCM SMS, SQL, Sys Admin, Windows
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.
Reports
- SU - MachineVulnerabilityReport.rdl (2197)
- SU-MachineVulnerabilityReport2012.rdl (554)
- SU - MissingUpdatesByCollection.rdl (2221)
– Now Includes Count error Fix, Thanks to Russ
– Submitted by Max in Comments section, Untested by me if someone can confirm it works it would be appreciated.
June 30th, 2014 on 09:01
@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.
E.g.
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.
June 26th, 2014 on 19:26
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.
May 10th, 2014 on 10:57
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
May 9th, 2014 on 17:56
@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)
SELECT
CI_UniqueID,
Title
FROM
v_AuthListInfo
ORDER BY Title
CollectionList (Dataset)
SELECT
CollectionID,
Name
from
v_Collection
WHERE
CollectionID = ‘xxx’ — Your Desired Display Name
OR
Name Like ‘%blah%’
ORDER by Name
May 9th, 2014 on 13:28
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
April 1st, 2014 on 18:08
Got it. Thanks. Gary was right, if you look close enough at the single quotes in the query they are messed up.
April 1st, 2014 on 10:25
@ 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.
Ryan
March 28th, 2014 on 14:35
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!
E-mon
March 28th, 2014 on 05:15
Hello,
Can you please let me know what needs fixing with the quotes in ComputerInfo?
Thanks
February 28th, 2014 on 16:25
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.
SU-MachineVulnerabilityReport.rdl
SU-MachineVulnerabilityReport2012.rdl
February 28th, 2014 on 14:29
@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.
Ryan
February 28th, 2014 on 07:34
SU – MissingUpdatesByCollection.rdl (1653) does this report work on SCCM 2012 sp1 ?
February 28th, 2014 on 06:58
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?
thanks
January 28th, 2014 on 17:26
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.
January 28th, 2014 on 16:40
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.
Peter
January 10th, 2014 on 09:29
@Derrick,
Sorry for the delay in response.
Which report are you running? The 2012 version or the 2007 version?
Regards,
Ryan
December 12th, 2013 on 20:38
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?
November 20th, 2013 on 11:10
I’ve now updated the Files and added the 2012 Report. Thanks to all comments. Sorry for taking so long to do this.
November 20th, 2013 on 10:55
@Russ
Thanks for the fix
November 14th, 2013 on 11:08
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!
October 10th, 2013 on 08:56
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
September 16th, 2013 on 21:23
Thanks Max!
September 16th, 2013 on 21:07
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
September 16th, 2013 on 20:58
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
September 16th, 2013 on 11:56
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.
Ryan
September 14th, 2013 on 15:15
How do you customize the datasource and add the right DB to connect to in the RDL file?
**new to SSRS reporting
Thanks
March 21st, 2013 on 14:20
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.
Ryan
March 21st, 2013 on 13:51
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?
November 7th, 2012 on 17:27
Thank you for the report. Got it mostly working and it looks like it will fill a void very nicely
November 7th, 2012 on 15:48
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?
November 7th, 2012 on 13:09
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).
November 6th, 2012 on 21:30
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
November 6th, 2012 on 19:23
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.
Ryan
November 6th, 2012 on 14:18
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?
Thanks,
kevin
October 23rd, 2012 on 16:58
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.
October 23rd, 2012 on 16:46
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?
October 23rd, 2012 on 16: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.
October 23rd, 2012 on 16:09
@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.
Regards,
Ryan
October 23rd, 2012 on 15:53
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…
September 24th, 2012 on 13:36
@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.
Regards,
Ryan
September 21st, 2012 on 17:55
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!
April 4th, 2012 on 15:02
Screenshots added.
April 4th, 2012 on 12:50
Can you post some screenshots? :P