NINet.org

SCCM Patch Compliance Reports

by 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

:, , , , , , , , ,

43 Comments for this entry

  • Ryan

    @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.

  • Roger Hanna

    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.

  • Peter L

    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

  • Aaron M

    @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

  • Peter L

    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

  • E$

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

  • Ryan

    @ 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

  • E$

    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

  • Yan

    Hello,

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

    Thanks

  • Gary

    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

  • Ryan

    @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

  • Sakkie

    SU – MissingUpdatesByCollection.rdl (1653) does this report work on SCCM 2012 sp1 ?

  • Sakkie

    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

  • peter asp

    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.

  • peter asp

    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

  • Ryan

    @Derrick,

    Sorry for the delay in response.

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

    Regards,

    Ryan

  • Derrick

    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?

  • Ryan

    I’ve now updated the Files and added the 2012 Report. Thanks to all comments. Sorry for taking so long to do this.

  • Ryan

    @Russ

    Thanks for the fix

  • Russ Rimmerman

    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!

  • eswar

    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

  • Paul M

    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

  • Max

    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

  • Ryan

    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

  • Paul M

    How do you customize the datasource and add the right DB to connect to in the RDL file?

    **new to SSRS reporting

    Thanks

  • Ryan

    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

  • Aaron

    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?

  • Kevin Wornell

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

  • Brooke

    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?

  • Ryan

    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).

  • Kevin Wornell

    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

  • Ryan

    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

  • Kevin Wornell

    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

  • Ken

    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.

  • Ryan

    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?

  • Ken

    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.

  • Ryan

    @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

  • Ken

    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…

  • Ryan

    @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

  • Ben

    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!

  • Ryan

    Screenshots added.

  • ConfigMgr Admin

    Can you post some screenshots? :P

Leave a Reply

*

Spam Protection by WP-SpamFree