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 .
How does the report look like ?
Hope it helps!