Configmgr SSRS Report Patch Compliance Statistics Last 30 days

Year ago,I created CM07 report to know the patch compliance stats for active patches last 30 days.More info can be found from here.But this report was for configuration manager 2007 which will not work with Configmgr 2012 due to the changes in storing the information in SQL tables/Views.

I have created similar report for configuration manager 2012.This report will list down all active patches which are deployed during last 30 days .I have used the criteria to filter the days using patch released date(DateCreated).

image

SQL Code Used in SSRS Report:

select UI.Title, UI.BulletinID,CS.NumPresent as Installed,CS.NumMissing as Missing,CS.NumFailed [Failed],
CS.NumNotApplicable as NotApplicable,CS.NumUnknown as Unknown,
CONVERT(decimal(5,2),(100.00*(Cs.NumPresent+cs.NumNotApplicable)/Cs.NumTotal)) [Compliant %],
case UI.IsSuperseded
when 1 then 'Yes'
Else 'No' End as [Superseded],
case UI.Isexpired
when 1 then 'Yes'
Else 'No' End as [Expired],
case UI.IsDeployed
when 1 then 'Yes'
Else 'No' End as [Deployed],
ui.dateposted [Date Posted],UI.DateCreated,
Deadline=cdl.Deadline,
UCS.CI_ID,
UI.InfoURL
from v_UpdateComplianceStatus UCS
inner join v_UpdateInfo UI On UI.CI_ID=UCS.CI_ID
inner join v_Update_ComplianceSummary CS on CS.CI_ID=ucs.CI_ID
inner join v_R_System Sys on sys.ResourceID=UCS.ResourceID
inner join v_CICategoryInfo CI On CI.CI_ID=UCS.CI_ID
outer apply (
select min(a.EnforcementDeadline) as [Deadline]
from V_CIAssignment  a
join v_CIAssignmentToCI  atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=UCS.CI_ID
) cdl
where (DATEDIFF(Day,UI.DateCreated, GETDATE())) <=30
group by UI.BulletinID,UI.Title,cdl.Deadline,UI.InfoURL,IsExpired,IsSuperseded,dateposted,IsDeployed,UCS.CI_ID,UI.DateCreated,
CS.NumPresent,CS.NumMissing,cs.NumNotApplicable,CS.NumUnknown ,CS.NumTotal,Cs.NumFailed

 

Looking for RDL file ? Download it from here,uploaded to your SSRS Report folder and Run Smile

To know more about software update management and how does system become compliant in Configmgr refer http://blogs.technet.com/b/system_center_in_action/archive/2011/05/02/test.aspx

7 Responses to "Configmgr SSRS Report Patch Compliance Statistics Last 30 days"

  1. Hi Eswar,

    Do you know which stateid correspond to the failed state?

    If I see the report 'Compliance 7 - Specific software update states' that only has 4 state names as below

    Update is required (corresponds to NumRequired column)
    Update is installed (corresponds to NumPresent Column)
    Detection state unknown (corresponds to NumUnknown column)
    Update is not required (corresponds to NumNotApplicable column)

    But there is no status which corresponds to NumFailed column which could give a list of machines on which the update failed.

    Any idea?

    Reply
    1. Sorry guys.. There is no link or report Avilable to list the computer names. Haven't had time to look at it.will try to get that report later.

      Reply
  2. Hi Eswar,

    How do we get the actual list of machines which correspond to those numbers?

    In your example you have 14 machines which have 'Missing' status. How do we get the list of those 14 machines?

    Similarly the list of 'Not Applicable' and 'Failed' machines. I was planning on hyperlinking these numbers to another report which will have the machine names.

    Reply

Leave a Reply