Jump to content


  • 0
Eswar Koneti

Patch Management report statistics in SMS /SCCM

Question

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

 

 

 

Patch.JPG

 

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

 

Patch Result.JPG

 

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

SCCM.JPG

Share this post


Link to post
Share on other sites

Recommended Posts

  • 0

Just working on the patch report to make it more simplied for the deployed patches for the month so though of sharing it here.

Looks like the one posted above is wasty or it gives some unwanted information as well.This report doesnt require any inputs,you just need to edit the script(like summ.ID='MS10-006') to provide the Bulletin ID's which are all you patches in ur environment.

after you run the report,it looks like below ,The same report still works in SCCM 2007 as well in similar way.

 

Patch Result.JPG

 

attached query report. Patch Statistics.txt

Still if you want to customise more in a good way to present for management can be found here http://eskonr.wordpress.com/2010/03/25/monthly-patch-statistics-reports-to-show-up-to-the-management-in-a-simplified-manner/

Share this post


Link to post
Share on other sites

  • 0

hi,

 

maybe you can help me out on this dilema im having, when I run a compliance report to see if my patches are installing fine. I’m seeing that they are installing on half my computers but I’m getting a lot of machines that fall under the "Detection State Unknown" and to make matters worse I actually remote to some of those machines using ConfigMgr to eliminate communication and I see that patches have not hit that machine since Feb24th.

 

Any Ideas?

Share this post


Link to post
Share on other sites

  • 0

hi lucent6408d, please post your issue as a separate topic and if anyone can answer it they will, posting the same question on two vaguely semi related topics will not get your issue looked at any quicker

Share this post


Link to post
Share on other sites

  • 0

Hi,

Thanks for your quuery. I would like to have the host names in this report?

 

My question is : i would like to have all the host names in this list for compliance machines , could you help me on these?

 

Thabks in advance.

 

Syed.

Share this post


Link to post
Share on other sites

  • 0

Hi,

Thanks for your quuery. I would like to have the host names in this report?

 

My question is : i would like to have all the host names in this list for compliance machines , could you help me on these?

 

Thabks in advance.

 

Syed.

 

Have you taken look at this post ? http://eskonr.wordpress.com/2010/03/25/monthly-patch-statistics-reports-to-show-up-to-the-management-in-a-simplified-manner/ which will gives you report about selected patches inbrief with the status.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...



×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.