Another Patch statistics report for SCCM 2007
Posted by Eswar Koneti on May 16th, 2011
Here is another patch statistics report for SCCM 2007 environment with summary of patches that are deployed within month (30 days) with different column.
select ‘Total number of active patches within 30days:’, COUNT(distinct Title) AS ‘Count’
FROM v_GS_PatchStatusEx
WHERE (DATEDIFF(Day, LastStatusTime, GETDATE())) <=30
UNION
select ‘Percent sucessfully installed’, round(100.0*COUNT( case when LastState=107 or LastState=105 then ResourceID else NULL end)/COUNT(ResourceID),1) as ‘Procent succesful’
FROM v_GS_PatchStatusEx
WHERE (DATEDIFF(Day, LastStatusTime, GETDATE())) <=30select ps.ID, ps.QNumbers, ps.Title,
round(100.0*COUNT(distinct case when ps.LastState=107 or ps.LastState=105 then ps.ResourceID else NULL end)/COUNT(distinct ps.ResourceID),1) as ‘Procent succesful’ ,
COUNT(distinct case when ps.LastState=107 or ps.LastState=105 then ps.ResourceID else NULL end) as ‘Distribution Successful’,
COUNT(distinct case when ps.LastState=101 then ps.ResourceID else NULL end) as ‘Distribution Failed’,
COUNT(distinct case when ps.LastState not in (107,105,101) then ps.ResourceID else NULL end) as ‘Distribution Incomplete’,
COUNT(distinct ps.ResourceID) as ‘In Distribution Scope’,
‘SMS00001′ as ‘CollectionID’,
‘Microsoft Update’ as ‘Type’,
inf.InfoPath
from v_GS_PatchStatusEx ps
join v_FullCollectionMembership fcm on ps.ResourceID=fcm.ResourceID
join v_ApplicableUpdatesSummaryEx inf on
ps.UpdateID=inf.UpdateID
where fcm.CollectionID= ‘SMS00001′ and
inf.Type = ‘Microsoft Update’
AND (DATEDIFF(Day, ps.LastStatusTime, GETDATE())) <=30
group by ps.ID, ps.QNumbers, ps.Title, inf.InfoPath