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).
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 
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 Comments
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?
you should look at v_Update_ComplianceSummary,it has numfailed value.
Dear Eswar,
how can I access that TechNet gallery? Could you provide the link?
Un saludo,
Pablo
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.
Thanks for your reply Eswar,
I can't find the link though, can you please share again?
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.
Nope, i haven't created any hyperlink report yet to list the computer names.will try to get that part later may be.