Introduction:
Software update compliance is a critical aspect of maintaining a secure and well-functioning IT environment. However, reporting the compliance status for a large number of clients can be time-consuming and challenging. In this blog post, we will explore an efficient method to quickly determine the compliance status of clients for specific software update groups. By leveraging custom reports in System Center Configuration Manager (SCCM), we can provide a concise summary of the targeted, missing, and required patches for each client, ultimately indicating whether they are compliant or non-compliant in a single line.
The Need for Efficient Compliance Reporting:
When it comes to software update compliance reporting, management and security teams are primarily interested in knowing whether the computers are fully patched or not. They require a clear indication of compliance without delving into the specific missing updates. Default compliance reports, such as "Software Updates - A Compliance--Compliance 5 - Specific computer," can provide detailed information, but extracting the desired summary quickly becomes a tedious task.
Understanding the Reporting Challenges:
The two commonly used default reports, "Compliance 1—Overall Compliance" and "Compliance 5 - Specific computer," have limitations in providing a concise compliance status for all deployed patches. The former only reports on a specific software update group for a given collection, while the latter requires manual filtering of missing/required patches to assess overall compliance.
Simplifying Compliance Reporting:
To address these limitations, we need a custom report that can quickly determine the compliance status of clients for all approved (deployed) patches. The report's logic relies on the number of required patches and the number of deployed (approved) patches. By following a set of rules, we can determine whether a client is compliant or non-compliant in a single line.
The Report's Logic: The logic used to determine compliance in the report is as follows:
- If the count of required patches is 0 and the count of deployed (approved) patches is 0, the client is compliant.
- If the count of required patches is not 0 and the count of deployed (approved) patches is not 0, the client is non-compliant.
- If the count of required patches is not 0 and the count of deployed (approved) patches is 0, the client is compliant.
Understanding the Report's Filters:
The report's SQL query includes filters to achieve the desired compliance reporting. The primary filter checks if the status is 2, indicating missing/required patches. Based on this criterion, the report calculates the total number of patches deployed to each client and how many are still needed.
Customizing the Report:
The report provided in the article can be downloaded from the TechNet TechNet Gallery here. After uploading the report to your SCCM SSRS Reports, make sure to change the Data Source as required. Running the report will generate a clear summary of compliance status for the specified collection, displaying the number of targeted, missing, and required patches, along with the final compliance status.
How does the report look like ?
Hope it helps!
56 Comments
How can I download this report? I canot find it in the gallery
Hi,
please follow this blog post to download the report https://eskonr.com/2021/02/did-you-miss-to-download-your-samples-from-technet-gallery-here-is-how-you-can-do-it/
Thanks,
Eswar
Hi Eswar,
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.
It is let say combination of your earlier work:
http://eskonr.com/2016/02/sccm-configmgr-ssrs-report-how-do-i-quickly-tell-if-clients-are-compliant-or-not-for-all-the-approved-patches/ (but for one machine).
and
http://eskonr.com/2015/10/sccm-configmgr-sql-query-how-to-get-list-of-patches-required-by-specific-computer/ (But this one I can’t get it to work, getting no results with this query).
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).
Is is possible to do it?
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.
Thanks,
Eswar
Hi Eswar,
What does the user name value mean? Is it the last logged on user? Or the user that has logged in the most recently on the device? What is the definition of the value?
Would be awesome if I could get an answer in the next coming days.
Thanks in advance!
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
Hi Ben,
Do you mean ,you want to exclude the patches that are released since 30 days old but include all patches older than 30 days from report run date ?
if so ,need to look at the SQL code to update the formula .Will try to get this when i find time this week hopefully 🙂
Regards,
Eswar
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?
Hi Ben,
yes this can be done however it takes time to reframe the SQL code and get it working .
You can use this report to specify the date from when to when to know the compliance status http://eskonr.com/2017/07/sccm-configmgr-software-update-compliance-report-for-specific-collection-within-specific-time-frame/
Regards,
Eswar
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.
Thanks for providing the knowledge. I would like to try your report, but stuck at changing "Data Source" What do i need to change the Data Source to? Thank you
Hi Mike,
you can follow the blog post https://www.enhansoft.com/blog/how-to-upload-a-ssrs-report-and-change-its-data-source
Regards,
Eswar
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 .
Yes ,this can be achievable but due to time constraints ,could not spend time on this.
Regards,
Eswar
Hi.
Is it ok that report for 700 workstations generates 6 minutes?
Hi ,
Nope it is not normal .You need to look at SSRS why it takes so long time to generate report .I ran report for 1000+ systems and it is faster and < minute. Regards, Eswar
I have this delay also in SQL management studio. Other default and your reports run fast.
Ok ,let me rewrite the query to improve the execution time.
Regards,
Eswar
Hi. Thanks for great report. Is it possible to report only on Microsoft patches?
To add only microsoft patches ,you need to edit the RDL file to change the code .
Regards,
Eswar
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.
Regards,
Eswar
Thank you for the reply.
Hi Eswar,
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.
The logic used in other report for missing patches is
missing patches =(dbo.v_UpdateComplianceStatus.Status = 2) AND (dbo.v_StateNames.TopicType = 402) AND (dbo.v_R_System.Netbios_Name0 = @computername).
The difference you have used in logic is distinct ui.title right. Is UI.title necessary ? I have used multiple combinations of CITypeID but the results are not matching
( select vrs.ResourceID,COUNT (distinct ui.title) isRequired
from V_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
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
and (ui.CIType_ID = 8 OR ui.CIType_ID = 10 OR ui.CIType_ID = 6 OR ui.CIType_ID = 2 OR ui.CIType_ID = 0)
group by vrs.ResourceID) DP on dp.ResourceID=vrs.ResourceID
Any thoughts would be appreciated
Thank you.
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.
CIType_ID TypeName
1 Software Updates
2 Baseline
3 OS
4 General
5 Application
6 Driver
7 Uninterpreted
8 Software Updates Bundle
9 Update List
10 Application Model
11 Global Settings
13 Global Expression
14 Supported Platform
21 Deployment Type
24 Intend Install Policy
60 Virtual Environment
Regards,
Eswar
Hello Eswar,
Many thanks for the prompt response and your time. You are running your blog in a great way helping out so many professionals sharing your valuable knowledge.
You are right, i cross checked with the default compliance report. I am getting the correct 'Isrequired' count when I limited the script to CITYPE _ID to just 1 or 8
ui.CIType_ID = 8 OR ui.CIType_ID = 1
I have three questions here:
1. Now When I cross check with default sccm report, ISrequired count is matching but the Approved(Deployed) count is not matching. Any thoughts? Do I have to change anything for this one here
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='XXXXXX'
group by vrs.ResourceID) REQ on req.ResourceID=vrs.ResourceID
WHERE ccm.CollectionID='XXXXXX'
2. I know you told me -Approved count in the report means ,patches that are deployed to the collection the client is member of.
In my scenario an ADR runs every month so I wonder how can the approved count be so less when it is running every month and every month patches are getting deployed to the machines.. Is it showing only the current month approved patches? or is it the cumulate count?
2. Why do you think the below logic is not giving accurate 'IsRequired' results when compared to yours. As most of the other reports online are using the below logic
missing patches =(dbo.v_UpdateComplianceStatus.Status = 2) AND (dbo.v_StateNames.TopicType = 402) AND (dbo.v_R_System.Netbios_Name0 = @computername
Thanks again.
Hi Eswar,
Any thoughts on approved count not matching with the default reports in sccm?
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.
Regards,
Eswar
Hi Eswar,
I doubled checked the approved count from my default reports. Approved count is not matching.
from v_r_system vrs
join v_ClientCollectionMembers ccm on ccm.ResourceID=vrs.ResourceID
join v_GS_OPERATING_SYSTEM OS on os.ResourceID=vrs.ResourceID
join v_GS_WORKSTATION_STATUS WS on WS.ResourceID=vrs.ResourceID
join v_updatescanstatus USS on uss.ResourceID=vrs.ResourceID
join ( select vrs.ResourceID,COUNT (distinct ui.title) isRequired
from V_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
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
and (ui.CIType_ID = 1 OR ui.CIType_ID = 8)
group by vrs.ResourceID) DP on dp.ResourceID=vrs.ResourceID
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
Hello,
Thank you for a great post. Is it possible to make a collection for machines to appear in that require a certain update?
You can but it require but what are you trying to here ? to create a collection ,you need to find the right wmi namespace/classes .
Regards,
Eswar
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.
Regards,
Eswar
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 .
Regards,
Eswar
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.
Regards,
Eswar
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.
Yes you can edit the rdl file using visual studio or report builder and give static collection id.
Regards
Eswar
That's what I've tried but no luck. Can you point me into the right direction? Maybe a simple report which I can analyze?
I will find time to update the SQL query in the blog .Allow some time.
Thanks,
Eswar
That would be awesome Eswar, I'll wait!
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 🙂
Sorry,
this is a reply to Eswar post at February 10, 2016 at 5:34 PM
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 .
No, both exactly same Information. A quick scan through the result showed, that not all Clients are duplicated - round about 60-70%.
Any tips?.
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.
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.
thanks for the feedback. I have updated the report ,please download and test it.
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.
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 ?
when server says ,non-compliant,it means there are some patches that are approved for the client but not installed .for the non-compliant Pc's ,you must troubleshoot by looking at logs. Refer the blog post which might help you to understand http://eskonr.com/2015/04/sccm-2012-troubleshoot-client-software-update-issues/
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
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 ?