SCCM Configmgr SSRS Report Quick way to check if Clients are compliant or not for all the approved patches?

Another interesting report  on software update compliance which will really helps lot of people in their daily job . Did you ever receive requests to report compliance status(in one line report) for bunch of clients for ex: specific collection ? Management or Security guys do not really care about what software updates ,the computer is missing and they always look for final results i.e Compliant or Non-Complaint .Only these 2 status matters for them to ensure the computers are fully patched.

Generally ,how do you check when such requests comes ? Look at default the compliance reports (Software Updates - A Compliance--Compliance 5 - Specific computer ) or other compliance reports?

There are 2 ways to tell if the client is complaint or not using the default reports.

1) You can run the report based on software update group for specific collection—Compliance 1—Overall Compliance

2) Compliance for specific computer--Compliance 5 - Specific computer 

Report 1) will give you only the status for specific software update group for specific collection but not for all software updates deployed to specific PC. A PC might have 100 software updates deployed and these software updates coming through multiple software update groups.it will be really challenging to find if PC is compliant for all the Deployed patches unless you have only one software update group.

Report 2) will give you list of all updates with targeted (approved) ,missing and installed but to tell if the PC is compliant for all the patches you see in the report takes time for you to filter the missing/required column and compare it with targeted patch. (Note:You only care about the Deployed patches but not all the updates required by PC,more about it will discuss soon).

Note: This post is strictly talking about software updates compliance but not for Endpoint Protection updates.

If you want to run the report 2) for bunch of PC’s ,you need to create custom report and it is not easy to say in one line whether Client is Compliant for all the approved (deployed) patches.

So,how do I check quickly, if PC is compliant for all the approved (deployed) patches meaning,if I enter the Collection name into the Report,it should give me status for the clients with how many Patches Targeted ,how many patches missing and how many are still required with final status IsComplaint or not ?

Before we go into the report ,i wanted to tell you how the logic build for this report to give final status if the client is compliant=Yes or No in one line.

If Count of Required Patches =0 and Count of Deployed(Approved) Patches=0 Then PC is Complaint

If Count of Required Patches!=0 and Count of Deployed(Approved) Patches!=0 then PC is Non-Complaint

If Count of Required Patches!=0 and Count of Deployed(Approved) Patches=0 then PC is Complaint

Before you proceed to download the report,you need to understand few things how this SQL query is written and what filters are used in the report to achieve the task.

Client becomes Non-Complaint only when there are updates needed by the PC otherwise ,we can say PC is Complaint. So my primary filter (where condition ) used here is to check if status=2 means required/missing from V_updateCompliancestatus. Based on this criteria,I have calculated the total number of patches deployed to the PC and how many are still needed by the PC.

You may wonder on the 3rd logic, how come the count of required patches!=0 and count of deployed(approved) patches=0 makes the client become compliant? Well,the count of required patches will surely be lot based on what update classification and products you have choose in SUP properties and not all organisations will deploy everything what you see in SCCM. Some organisations will only deploy critical ,security with severity critical and important and ignore low and medium severity based on the requirements from security team.

I have not made any custom filtering on the required count ,i just bring all the updates what client requested for. If you want to exclude the updates that are not deployed by your organisation ,you can edit the report for custom changes.

so you only need to worry about the approved(deployed) count and made sure if always 0 to get your client PC compliant. If there are any updates requested by client and some of them are approved and if they appear in report ,client will become non-compliant.

All clear now ? if you still have questions,please report them via comments section .

Download the RDL file from TechNet Gallery here, upload the report into your Configmgr SSRS Reports ,change the Data Source and run the Report Smile .

How does the report look like ?

image

Hope it helps!

25 Responses to "SCCM Configmgr SSRS Report Quick way to check if Clients are compliant or not for all the approved patches?"

  1. HI Eswar. I downloaded and tried your report, but I'm a bit confused as to what it is showing me (maybe I'm being a bit dumb). What I want to see is machines that have updates 'approved' (i.e. deployed), against updates that are installed. So that you can tell if some updates are deployed but NOT installed. I thought from your description that that is what your report does, but now I'm not s sure. It seems to show required against deployed - which isn't really any use as I know that a lot of those will be missing because (for example) we don't always deploy patches in the 'update' category.

    Reply
    1. What I want to see is machines that have updates 'approved' (i.e. deployed), against updates that are installed. ? who cares about what patches installed instead what is approved and what is pending is matters for anyone.
      This report will give you count of approved updates and out of these approved updates ,how many are still required . this will show ,you still have Clients to troubleshoot the required patches.

      Regards,
      Eswar

      Reply
      1. But that's my point - I don't think your report is doing what you said it is. My own PC is in my test collection for software updates (which i deploy monthly patches to first). If i go into Software Center, there are no outstanding patches - but your report shows 100% NON-compliance against that collection and my pc shows as having 54 updates installed and 119 required. So something is not right. Either there should be about 50 updates showing in Software Center or the numbers in your report are wrong.

        Reply
        1. Hi,
          I THINK you have read the report incorrectly .The number of updates showing as Deployed means they are approved for the Client which are yet to be installed and isrequired is total number of updates (irrespective of classification ,it can updates ,security ,service pack etc) the client requested for the updates available in SCCM.
          If you are sure that ,my report is showing wrong compliance ,can you check run default report Software Updates - A Compliance > Compliance 5 - Specific computer and get the count of required updates and approved updates ,validate with my report count .

          Regards,
          Eswar

          Reply
          1. But your 'deployed' count also seems to include the updates that are already installed. So it is not telling me the difference between updates that are deployed and have been installed, and updates that are deployed but have not been installed. That's the bit we really want to see - what updates have been deployed but are still waiting to be installed. Updates that are 'required' are of no interest because they include lots of updates that we haven't approved in the first place.

            Reply
            1. But your 'deployed' count also seems to include the updates that are already installed --nope, report doesn't include updates that are installed on the client.Report only talks about count of updates approved (deployed) and count of updates required ,thats it.
              Did you ran the default report that i said in previous comment to compare the results with my report ? if you find anything false ,let me know.

              Regards,
              Eswar

  2. Is it possible to modify this report to NOT to ask for a collection but use a fixed CollectionID instead? I need to include this report in a webpage.

    Reply
  3. Hey,
    i've used the new one, but still get the issue.
    Computer1 Username Microsoft Windows 7 Professional Jan 27 2016 4:45PM Jan 28 2016 4:11PM Yes 0 1 Complaint
    Computer1 Username Microsoft Windows 7 Professional Jan 27 2016 4:45PM Jan 28 2016 4:11PM Yes 1 1 Non-Complaint
    Computer2 User Microsoft Windows 7 Professional Feb 11 2016 8:32AM Feb 11 2016 9:20AM Yes 0 1 Complaint
    Computer2 User Microsoft Windows 7 Professional Feb 11 2016 8:32AM Feb 11 2016 9:20AM Yes 1 1 Non-Complaint

    Thanks for your help 🙂

    Reply
    1. weird ,any chance that,the usernames are different for each PC or its same ? never had such conflict at my customers or even blog readers. all works fine .

      Reply
        1. i tested this query in different environments ,all works good .Really need to look at whats happening. Can you export the results after you run the SQL Query ,i will import that into SQL and execute it ,i am sure it will give unique results but something is happening at your side which i cannot tell without looking at it.

          Reply
  4. Hi, Eswar
    thanks for your amazing report. I downloaded it and change only data source. if I run this report its look like so.

    PC Name User Name0 OS Last HWScan Last SUScan Last SUScan Success Targeted Is Required Status
    BBGDVDRVAP1 Microsoft Windows Server 2008 R2 Standard Feb 7 2016 6:37PM Feb 8 2016 4:06PM Yes 0 1 Complaint
    BBGDVDRVAP1 Microsoft Windows Server 2008 R2 Standard Feb 7 2016 6:37PM Feb 8 2016 4:06PM Yes 0 0 Complaint
    BBGDVDRVAP1 Microsoft Windows Server 2008 R2 Standard Feb 7 2016 6:37PM Feb 8 2016 4:06PM Yes 1 0 Non-Complaint
    BBGDVFSP1 sys-vdeger Microsoft Windows Server 2012 R2 Standard Feb 9 2016 4:48AM Feb 8 2016 3:54PM Yes 0 1 Complaint
    BBGDVFSP1 sys-vdeger Microsoft Windows Server 2012 R2 Standard Feb 9 2016 4:48AM Feb 8 2016 3:54PM Yes 0 0 Complaint
    BBGDVFSP1 sys-vdeger Microsoft Windows Server 2012 R2 Standard Feb 9 2016 4:48AM Feb 8 2016 3:54PM Yes 1 0 Non-Complaint

    I report I can see compliant compliant and non-compliant for same server.
    what I am doing wrong ?
    thanks for helping.

    Reply
      1. thanks for new report and its work fine now 🙂
        but if i check 2-3 compliant server from report result, the patches are not installed on server.
        last hw and su scan dates are today.

        Reply
        1. do you see any patches that are targeted and isrequired=0 ?what do you see for these 2 columns ? if you see ifrequired count>0 and targeted=0 ,you can simply ignore it as you havent deployed any of the isrequired patches to the client to action on them.

          Reply
        2. do you see any patches that are targeted and isrequired=0 ?what do you see for these 2 columns ? if you see isrequired count>0 and targeted=0 ,you can simply ignore it as you havent deployed any of the isrequired patches to the client to action on them.

          Reply
          1. no i cant see targeted and isrequired=0, i see targeted=0 and isrequired=1 its say compliant and see targeted=1 and isrequired=1 its say non-compliant.
            for me is important report say compliant but if i check on server Control Panel\All Control Panel Items\Windows Update\View update history and Installed Updates from control panel i cant see patches are installed . why i cant see the server non-compliant on report ?

            Reply
      2. Hi,

        great thanks for this Report. I still get with the updated Report both, compliant and not-compliant for the same Workstation.

        thanks for help

        Reply
        1. you should not with new report.download it and test it again,If you still get, can you post the entries for the machine showing complaint,non-complaint entries ?

          Reply

Leave a Reply