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 .

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

Found your blog and I see you are doing great things. You helped so many people.

These days I got the task to do compliance report for machine.
Idea is to start this report on local computer in case if it is needed. We are planning to implement this with our solution, which is closely connected to Microsoft SCCM.

Quick Compliance Check for Computer (per one machine).

My task is to check in this report if Client is compliant or not for all the approved patches, if it is not to list all missing patches.

Ideal report would start with Hostname and some basic information provided about the machine, like: System Manufacturer, System Model, BIOS Version, CPU Name, Operating System version with build number, Total RAM (GB), Disk, Memory Information, Network Information.

There could be also graph - green or red light based on status if computer is compliant or not.

And down below list of missing updates which need to be installed to get this computer fully compliant with all updates and latest Windows version (build).

Hi,
Yes ,you can do it but this is full of customized report .You can make use of the existing report on this blog and create new one.
Due to time limitations ,i could not able to get the SQL stuff these days due to other priorities.

love it! Can you get super cool by adding in a tolerance for compliance? i.e exclude patches that are not yet 30 days old since released. This will give us a better compliance statistic because all patches have to be installed within 30 days in our environment

Hey Eswar, thanks for replying. Exactly that! When I run a compliance report, I would like the option of specifying what my compliance period is. My servers will be compliant if required patches are newer than x days but not compliant if the patches required are older than x days. Does that make sense?

Thanks Eswar, ill take a look. That would be an uber report if you could get it working! Im sure many an admin would love to see this option on the report in this post. Thanks for taking the time to answer my question.

Hi,
maybe you have also drill-down report for each system to see what updates are missing? I tried but its hard to understand which sql views you are using. So much abbreviations 🙂 For instance link with built in report - Compliance for specific computer--Compliance 5 - Specific computer .

Hi Eswar
Many thanks for sharing knowledge.
As you mentioned we can edit the 'Isrequired' section as per the organization standard. If We just use security and critical updates, which parameter in the code should i be changing to suit that requirement? What does CI_type value exactly mean?

My second doubt what do you mean when you say 'Approved' Updates. Is it all the updates client scans and requests sccm?

Hi Kar,
For security and critical updates you need to use filter to limit the update classification i.e 8,9 i think for security and critical updates but you can check in SQL view in report to get correct value.
when i say Approved in the report means ,they are deployed to the collection the client is member of.

I might be wrong on this but sharing my results so that you can correct me. I have tried finding required count from multiple reports The value from the other reports match mutually but not with this report.

Hi,
you need to understand what CIType_ID is and what each value refers associated with it. Listed below are the CIType ID and its description. So if you look at my query for required updates ,i have only used ui.CIType_ID = 1 OR ui.CIType_ID = 8 with css.Status=2 which is missing/required. So my query is trying to pull information with citype assosicated with software updates and Software Updates Bundle which is what you will need for . Am not sure why do you use other CItype IDs for software updates here.They play no role. Take a single client ,run my report,identify the required updates count, keep it aside .Now run the default reports to know the compliance status for specific machine ,there you will get list of all updates ,sum up them ,check if the count of required updates from default report and my report is same or not.

Hi,
I have checked it on different environments ,it works good. Did you export the default report for specific machine and filter with required,approved count of updates ?
Also check the query what is used in the RDL file for approved to verify it.

join ( select vrs.ResourceID ,count(ctm.ResourceID)as Deployed
from V_UpdateComplianceStatus css
left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
JOIN dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID
join v_ClientCollectionMembers ccm on ccm.ResourceID=css.ResourceID
where css.Status=2 and ccm.CollectionID=@COLLID
group by vrs.ResourceID) REQ on req.ResourceID=vrs.ResourceID
WHERE ccm.CollectionID=@COLLID
group by vrs.name0,vrs.User_Name0,os.Caption0,ws.LastHWScan,uss.lastscantime,uss.LastErrorCode,REQ.Deployed,DP.isRequired
ORDER BY 7

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.

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.

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.

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 .

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.

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.

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.

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.

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.

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 ?