Patch Management report statistics in SMS /SCCM

[Eswar Koneti]

Hi,

There are many default reports that gives you the complaince report for the patches .But Here is the simple report which will be easier to find the success rate of the deployed patches .so thought sharing with you. Create a new report with the below given query.In this report ,i just shows you how to pull the statistics for th given 2 patches.You can edit the qeury to have multiple patches.

select summ.Product, summ.LocaleID, summ.Language,@ID as 'KB Number',
  	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=102 then ps.ResourceID else NULL end) as 'Reboot Pending',
  	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,102) then ps.ResourceID else NULL end) as 'Distribution Incomplete',
  	COUNT(distinct ps.ResourceID) as 'In Distribution Scope',
ROUND(100.0*COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as 'Success %',
ROUND(100.0*COUNT(distinct case when ps.laststate=101 then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as 'failed %',
ROUND(100.0*COUNT(distinct case when ps.LastState not in (107,105,101,102) then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as 'Incomplete %'
from v_GS_PatchStatusEx ps
join v_ApplicableUpdatesSummaryEx summ on 
  ps.UpdateID=summ.UpdateID 
where ([email="summ.ID=@ID"]summ.ID=@ID[/email] or [email="summ.QNumbers=@ID"]summ.QNumbers=@ID[/email] or [email="summ.Title=@ID"]summ.Title=@ID[/email]) and
       	summ.Type='Microsoft Update'
group by summ.Product, summ.LocaleID, summ.Language 
order by summ.Product,  summ.LocaleID, summ.Language


select summ.Product, summ.LocaleID, summ.Language,@ID1 as 'KB Number',
  	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=102 then ps.ResourceID else NULL end) as 'Reboot Pending',
  	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,102) then ps.ResourceID else NULL end) as 'Distribution Incomplete',
  	COUNT(distinct ps.ResourceID) as C093,
ROUND(100.0*COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as 'Success %',
ROUND(100.0*COUNT(distinct case when ps.laststate=101 then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as 'failed %',
ROUND(100.0*COUNT(distinct case when ps.LastState not in (107,105,101,102) then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as 'Incomplete %'
from v_GS_PatchStatusEx ps
join v_ApplicableUpdatesSummaryEx summ on 
  ps.UpdateID=summ.UpdateID 
where ([email="summ.ID=@ID1"]summ.ID=@ID1[/email] or [email="summ.QNumbers=@ID1"]summ.QNumbers=@ID1[/email] or [email="summ.Title=@ID1"]summ.Title=@ID1[/email]) and
       	summ.Type='Microsoft Update'
group by summ.Product, summ.LocaleID, summ.Language 
order by summ.Product,  summ.LocaleID, summ.Language

 

Promot for ID and ID1:

 

begin
if (@__filterwildcard = '')
 select distinct Title, ID, QNumbers,Type from v_ApplicableUpdatesSummaryEx order by Title
else
 select distinct Title, ID, QNumbers, Type from v_ApplicableUpdatesSummaryEx
 WHERE Title like @__filterwildcard
 order by Title
end

 

When you run this report,it prompt you to enter(select) either patch number or MS ID number (best is to enter KB article which will be unique for each patch) looks like below

 

 

 

 

Once you this,it gives you a report which you are lookign for as below

 

 

The same report does work in SCCM as well but it doesnt give information about the product and language.Shown below for your information.

[Eswar Koneti]

Take a look at this post http://eskonr.com/20...plified-manner/ .there are some changes to the SQL views from SMS 2003 to SCCM 2007 environment. try the query posted on my blog.

You amy also refer this report for patch statistics sccm 2007 with summary of failed,installed etc http://eskonr.com/2011/05/another-patch-statistics-report-for-sccm-2007/

[ashish_k_g]

Hi

 

Thanks for all your advise, pls provide the same for SCCM 2012 RTM for creating all reports and steps also on below given id:

 

ashishdevu@gmail.com

[newnoob]

I know this is an old threat but my boss wants exactly report like this. Do you have a query that works for SCCM 2012 or CB 1706?

Thanks in advance,

newnoob

[Eswar Koneti]

Have you looked at the default software update compliance reports? they do provide similar but more granular.

 

Thanks,

Eswar