Compliance report, break down by months

Instead of maintaining endless Update Lists with patches from each month, I created this custom report, breaking down by Months.

Creterias:

1. Category: Security patches

2. Prodcuts: All

3. Severity: Critical and Important

4. One missing patch brings entire computer to incomplicane.

 

Here is the query (copy/paste it to the report)

 

SELECT D.MonthPosted, Compliant,
Incompliant, cast(Compliant / ( (Compliant + Incompliant) /100.0) as decimal(5,2) ) as PercentCompliant

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_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_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

Comments

• Anonymous
  August 26, 2014
  Thanks for sharing a nice report.  Any suggestions on how to limit the view to select devices/collections? The current report give all devices.  I'm looking to be able to view subsets (e.g. servers, desktops).