Eswar Koneti 29 Report post Posted February 25, 2010 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. Quote Share this post Link to post Share on other sites
anyweb 477 Report post Posted February 25, 2010 nice, keep them coming Quote Share this post Link to post Share on other sites
Eswar Koneti 29 Report post Posted February 25, 2010 Hi, When you try to paste the given code into your report,you might get error because of email and other stuff present in the ocde which i posted ,thiking this might be because of where summ.ID=@ID So i have attached the code to make it easier. Code.txt Quote Share this post Link to post Share on other sites
Eswar Koneti 29 Report post Posted March 25, 2010 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. 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/ Quote Share this post Link to post Share on other sites
lucent6408d 0 Report post Posted April 28, 2010 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? Quote Share this post Link to post Share on other sites
anyweb 477 Report post Posted April 28, 2010 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 Quote Share this post Link to post Share on other sites
lucent6408d 0 Report post Posted April 28, 2010 kk sorry about that Quote Share this post Link to post Share on other sites
FunnyStar 0 Report post Posted May 24, 2010 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. Quote Share this post Link to post Share on other sites
Eswar Koneti 29 Report post Posted May 24, 2010 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. Quote Share this post Link to post Share on other sites
FunnyStar 0 Report post Posted May 24, 2010 Its giving the error to define the @Title variable in sql??? Quote Share this post Link to post Share on other sites
Eswar Koneti 29 Report post Posted May 24, 2010 Its giving the error to define the @Title variable in sql??? Did you creat a prompt value for title with a query like (select Title,ID,QNumbers from v_GS_PatchStatusEx ) ? Quote Share this post Link to post Share on other sites
FunnyStar 0 Report post Posted May 24, 2010 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 Quote Share this post Link to post Share on other sites
Eswar Koneti 29 Report post Posted May 24, 2010 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 Quote Share this post Link to post Share on other sites
FunnyStar 0 Report post Posted May 25, 2010 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. Quote Share this post Link to post Share on other sites
Eswar Koneti 29 Report post Posted May 25, 2010 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. Quote Share this post Link to post Share on other sites
FunnyStar 0 Report post Posted May 26, 2010 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. Quote Share this post Link to post Share on other sites
mcsebala 0 Report post Posted August 22, 2011 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 Quote Share this post Link to post Share on other sites
TJT 0 Report post Posted October 13, 2011 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" Quote Share this post Link to post Share on other sites
TJT 0 Report post Posted October 13, 2011 What is the select statement for the @Status prompt? Quote Share this post Link to post Share on other sites
TJT 0 Report post Posted October 13, 2011 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. Quote Share this post Link to post Share on other sites
TJT 0 Report post Posted October 14, 2011 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 Quote Share this post Link to post Share on other sites
Eswar Koneti 29 Report post Posted October 25, 2011 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/ Quote Share this post Link to post Share on other sites
ashish_k_g 0 Report post Posted December 23, 2012 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 Quote Share this post Link to post Share on other sites
newnoob 0 Report post Posted June 28, 2018 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 Quote Share this post Link to post Share on other sites
Eswar Koneti 29 Report post Posted December 8, 2019 Have you looked at the default software update compliance reports? they do provide similar but more granular. Thanks, Eswar Quote Share this post Link to post Share on other sites