Sccm Configmgr 2012 Patch Compliance Summary Report Month Wise

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 ?

image

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

19 Responses to "Sccm Configmgr 2012 Patch Compliance Summary Report Month Wise"

  1. 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.

    Reply
      1. 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.

        Reply
  2. 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

    Reply
    1. 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.

      Reply
    1. 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.

      Reply
  3. 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)

    Reply
    1. 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.

      Reply

Leave a Reply