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.

[FunnyStar]

Did you creat a prompt value for title with a query like (select Title,ID,QNumbers from v_GS_PatchStatusEx ) ?

 

Hi,

 

He mentioned in the 2nd report for the Title value, while creating 3rd report itself its asking variable declartion (am creating 3->2->1). Even if i assign the varchar to the Title in the 3rd report showing as No Records found like that or empty values.

 

Help me

[Eswar Koneti]

Hi,

 

He mentioned in the 2nd report for the Title value, while creating 3rd report itself its asking variable declartion (am creating 3->2->1). Even if i assign the varchar to the Title in the 3rd report showing as No Records found like that or empty values.

 

Help me

 

 

I am not follwing you properly about the reports 3,2,1 .Okay can you follow up the attached document txt file to have it correctly and to have prompts and linking u can use the other website .Hope this makes u clear.

Status report quiries.txt

[FunnyStar]

I am not follwing you properly about the reports 3,2,1 .Okay can you follow up the attached document txt file to have it correctly and to have prompts and linking u can use the other website .Hope this makes u clear.

 

Hi,

 

Thanks for ur reply. am following ur query from the text file. first i created the 3rd report as in the website there am getting to declare the Title variable but in website its shows to create in 2nd report for Prompt. Could u pls try once again or tel me which report should i create first?

 

Looking forward from you.

 

Syed.

[Eswar Koneti]

Hi,

 

Thanks for ur reply. am following ur query from the text file. first i created the 3rd report as in the website there am getting to declare the Title variable but in website its shows to create in 2nd report for Prompt. Could u pls try once again or tel me which report should i create first?

 

Looking forward from you.

 

Syed.

 

Ahh ...I knew it now...From the 3rd report (Status of particular bulletin ID with selected distribution status:),i can see ,@title is delclared but asked to create prompt for ID

Now it is corrected .In both the reports you will have to create prompt value for Title.

And it doesnt matter to create which report first, at the end to have all the reports and linking has to be done to get the reports.

[FunnyStar]

Ahh ...I knew it now...From the 3rd report (Status of particular bulletin ID with selected distribution status:),i can see ,@title is delclared but asked to create prompt for ID

Now it is corrected .In both the reports you will have to create prompt value for Title.

And it doesnt matter to create which report first, at the end to have all the reports and linking has to be done to get the reports.

 

Thanks.Its worked like charm.

[mcsebala]

Hi

 

Nice report from you.can please provide a step by step guide with screehshots for the customised reports.this will be very useful for me.

 

my mail id mcsebala@yahoo.co.in

 

 

regards

bala

[TJT]

I can't seem to get this report working in SCCM 2007. It returns nothing. I have changed MS10-006 to MS11-054 etc and still no luck.

 

Next I tried this "where (summ.qnumbers='KB2539639' or summ.qnumbers='KB2507938') "

 

and this

 

"where (summ.qnumbers='2539639' or summ.qnumbers='2507938') "

 

Still no luck ... no matching records could be found

 

"select summ.Product,summ.Language,summ.ID,summ.qnumbers as'Q 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 'Requested',

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 (summ.ID='MS10-006' or summ.ID='MS10-007' or summ.ID='MS10-008' or summ.ID='MS10-013') and

(summ.Type='Microsoft Update') and (summ.product not like 'Windows Server%')

group by summ.Product, summ.ID, summ.Language, summ.qnumbers

Order By summ.Language,summ.ID"

[TJT]

What is the select statement for the @Status prompt?

[TJT]

I was also trying to figure out to get KB articles from an Update List by joining v_AuthListInfo (The Update List) and v_UpdateInfo (The list of KB articles). This way if you had an update list with September patches, for example, and you grabbed the KB article list and used this list to determine your patch compliance for a given month of MS Updates.

[TJT]

The following will grab the KB articles:

 

select distinct v_UpdateInfo.ArticleID

from v_AuthListInfo,v_CIRelation,v_UpdateInfo

where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and

v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and

v_AuthListInfo.Title = @UPDATELIST

order by v_UpdateInfo.ArticleID

 

 

@UPDATELIST PROMPT:

 

 

begin

if (@__filterwildcard = '')

select distinct Title from v_AuthListInfo order by Title

else

select distinct Title from v_AuthListInfo

where ((CI_UniqueID like @__filterwildcard) or

(Title like @__filterwildcard))

order by Title

end