If you search on the web for Configmgr patch compliance report ,you get various links that talk about compliance reports ,you utilize them to start working on the customized report the way you want but i was looking for something that give me the overall compliance status for each month ? search did not provide me much useful information on this ,I then decided to create and share to public.
How do you monitor the overall patch progress for the current month and its previous months ? let say,for the current month,compliant machines are 500(which have got all approved patches) and noncompliance and its % in one line ?
if you look at the below screen,you got something better which you can take this to security or management guys what you have done so far with configmgr ?
Not sure how many of you already seen post from Alexsemi who has developed report by month wise since from beginning(based on the patch released date ).
Criteria's used in the query:
1. Category: Security patches
2. Products: All
3. Severity: Critical and Important
4. One missing patch brings entire computer to noncompliance.
If you are into configuration manager 2007 and using classic report feature,you can use the below SQL code which is also available here to download .
if you are into Configuration manager 2012 ,you will have to create SSRS report ,for which i have uploaded the .RDL file here ,so download and upload it to your SSRS reports.
SELECT D.MonthPosted , Compliant , Incompliant [Non-Compliant],Compliant + Incompliant [Total], cast(Compliant / ( (Compliant + Incompliant) /100.0) as decimal(5,2) ) as [Compliant %],
cast(Incompliant / ( (Compliant + Incompliant) /100.0) as decimal(5,2) ) as [Non-Compliant %]
FROM
(
Select MonthPosted, Count(1) as Compliant FROM
(
SELECT MonthPosted, ResourceID
FROM (SELECT TOP (100) PERCENT v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,
v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)
AS MonthPosted, COUNT(1) AS Count
FROM v_UpdateComplianceStatus INNER JOIN
v_UpdateInfo ON v_UpdateComplianceStatus.CI_ID = v_UpdateInfo.CI_ID INNER JOIN
v_R_System ON v_UpdateComplianceStatus.ResourceID = v_R_System.ResourceID
WHERE (v_R_System.Obsolete0 = 0 AND v_R_System.Client0 = 1 AND v_R_System.Active0 = 1) AND (v_UpdateInfo.Severity IN (8, 10)) AND (v_UpdateInfo.IsSuperseded = 0) AND (v_UpdateInfo.IsEnabled = 1)
GROUP BY v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,
v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)) A
where Status =3
AND not exists
(
SELECT B.MonthPosted, B.ResourceID
FROM (SELECT TOP (100) PERCENT v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,
v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)
AS MonthPosted, COUNT(1) AS Count
FROM v_UpdateComplianceStatus INNER JOIN
v_UpdateInfo ON v_UpdateComplianceStatus.CI_ID = v_UpdateInfo.CI_ID INNER JOIN
v_R_System ON v_UpdateComplianceStatus.ResourceID = v_R_System.ResourceID
WHERE (v_R_System.Obsolete0 = 0 AND v_R_System.Client0 = 1 AND v_R_System.Active0 = 1) AND (v_UpdateInfo.Severity IN (8, 10)) AND (v_UpdateInfo.IsSuperseded = 0) AND (v_UpdateInfo.IsEnabled = 1)
GROUP BY v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,
v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)) B
where Status =2
and B.MonthPosted = A.MonthPosted and B.ResourceID = A.ResourceID
Group By MonthPosted, ResourceID
)
Group By MonthPosted, ResourceID
) C
Group By MonthPosted
) D,
( SELECT MonthPosted, Count(1) as Incompliant
FROM (SELECT TOP (100) PERCENT v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,
v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)
AS MonthPosted, COUNT(1) AS Count
FROM v_UpdateComplianceStatus INNER JOIN
v_UpdateInfo ON v_UpdateComplianceStatus.CI_ID = v_UpdateInfo.CI_ID INNER JOIN
v_R_System ON v_UpdateComplianceStatus.ResourceID = v_R_System.ResourceID
WHERE (v_R_System.Obsolete0 = 0) AND (v_UpdateInfo.Severity IN (8, 10)) AND (v_UpdateInfo.IsSuperseded = 0) AND (v_UpdateInfo.IsEnabled = 1)
GROUP BY v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,
v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)) F
where Status =2
Group By MonthPosted ) E
where D.MonthPosted = E.MonthPosted
order by MonthPosted Desc
22 Comments
Pingback: SCCM 2012 Free tools – Just another WordPress site
yes, but i think I'm seeing the same problem. I'm seeing updates marked "required" by Microsoft that aren't in my SUG. In my SUG's I only include security and critical updates. But, I'm seeing some "required" updates classified as "updates" in that report.
http://eskonr.com/2015/01/sccm-configmgr-2012-ssrs-patch-compliance-report-per-collection-per-update-group/
Nice report! https://gallery.technet.microsoft.com/Configmgr-2012-SSRS-Patch-7a759744
How could you take this report and say, use a specific update collection for the compliance? My problem is that I need to target a specific update group for my compliance. How would you do that with this report?
have you tried this ? patch compliance report month wise for specific collection http://eskonr.com/2014/09/configmgr-2012-summary-of-patch-compliance-status-report-month-wise-for-specific-collection-ssrs-report/
I have, but for some reason, the information reported appears to be a reflection of the Compliance A over all compliance report against a collection. But, what I want is, what is the compliance of a specific SUG for a specific collection. What I'm finding is that most of the "standard" reports include updates that are not part of a SUG. So, I always get mix results with the canned reports. I tried the report you talked about and it appeared to reflect the over all compliance of an update group rather than the compliance of the updates in a SUG. Not sure if that makes sense or not.
you can try the report from http://eskonr.com/2015/01/sccm-configmgr-2012-ssrs-patch-compliance-report-per-collection-per-update-group/ ,this report will give you the % compliance for that specficic SUG and specific collection
Sorry - It worked, I was missing database connection configuration after uploading the file to server
Yes, you need to change the data source then it works.
I uploaded rdl file to my SCCM reporting server, but unable to run the report, it shows error that there are error occurred while running the report
Pingback: System Center Configuration Manager Community Tools | The Blue Trainers Team
Hi Eswar,
I am also looking for a patch compliance summery report month wise for specific update group. Could you help me on this
generally software update group (how others follow) is,to have monthly updates or quarterly updates into one SUP group and then deploy them to collection.
so coming to your request,what is the point to have month wise compliance for specific software update group ? how is your SUGs configured ?
there is also recommendation from Microsoft (that i read online) that, it is not recommended to have more than 1000 software updates to one SUG.
I am looking for a patch compliance summary report month wise but for a specific software update group.. any help would be much appreciated.
due to the time limitation,i will not be able to do some more customization for this but for sure,will try to get this done when time permits.Meantime,you can browse the reports available on my blog that will help you to get started.
Eswar,
This is a great start but the ability to limit the view (i.e by collection) is really needed. Overall compliance takes into account all devices, which sometimes are not patched (Citrix, Desktops, etc). Can you see anyway to offer a limiting collection of some sort while still showing it's compliance rates?
I've monkey'd around with it and am not familiar enough with SQL query to accomplish this. Perhaps an INNER JOIN of some sort? Here's a sample from another report that does this for me:
FROM v_UpdateComplianceStatus ucsa
INNER JOIN v_CIRelation cir ON ucsa.CI_ID = cir.ToCIID
INNER JOIN v_UpdateInfo ui ON ucsa.CI_ID = ui.CI_ID
LEFT JOIN v_R_System rs ON ucsa.ResourceID = rs.ResourceID
WHERE
cir.FromCIID=@AuthListLocalID
AND
cir.RelationType=1
AND
ucsa.ResourceID in (Select vc.ResourceID FROM v_FullCollectionMembership vc WHERE vc.CollectionID = @CollID)
Hi,
You mean to say that,you have other clients in sccm which are not to be patched and you need to exclude those from the report ? i can look into that after a while when time permits.
Pingback: Liste des outils pour Configuration Manager 2012 | Déploiement Windows
Can we get this report for a collection or multiple collections
yes but you dont get similar results ,to know the compliance % on specific collection for clients,you can refer http://ninet.org/2012/04/sccm-patch-compliance-reports/
Hi,
Is is possible to make this report specific to Month posted, like Month Posted will be provided as an DropDown list and based on the selection of Month Posted the compliance will be shown.
you can try something like and amend the changes to the above report . http://eskonr.com/2017/07/sccm-configmgr-software-update-compliance-report-for-specific-collection-within-specific-time-frame/