Monthly Patch statistics reports in SMS/SCCM to show up to the management in a simplified manner
Posted by Eswar Koneti on March 25th, 2010
Long back created a report for the monthly Patch statistics which can be found in http://www.windows-noob.com/forums/index.php?/topic/1764-patch-management-report-in-sms-2003/#entry6281
Below all the reports have been created using the last state messages.Even you can create different type of quiries based on this.
I was referring to the Patch process and found an image which gives the statistics for the listed patches in a good viewable way.so thought of creating such a report and can be linked to other report to get preferable colums which are necessary.May be i can show it to the management team for the patch activity on monthly basis.This basically requries to create 3 reports( like 1,2,3 ) out of which 3 is linked to 2 and 2 is linked 1.It is just simple that you can run only one report which is linked to other reports which gives u a report like below for the given bulletin ID’s.
Note:The below report is filterd with language swedish,if you want to get status for English/other language patches,you can customise it.The below report is called 1)Patch Management summary
To Build this report and to link to other reports ,you will have to create 3 reports which i named it like :1)Patch Management summary 2)Status of Each bulletin ID 3)Status of each bulletin ID with distribution status
Create new reports for each with the below query.
3)Status of particular bulletin ID with selected distribution status:
select distinct sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, fcm.SiteCode, ws.LastHWScan,
DATEADD(ss,@__timezoneoffset,ps.LastStatusTime) as LastStatusTime, PSX.TimeApplied0,ps.LastStatusMessageIDName, ps.LastExecutionResult
from v_R_System sys
join v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
join v_GS_WORKSTATION_STATUS ws on sys.ResourceID=ws.ResourceID
join v_GS_PatchStatusEx ps on sys.ResourceID=ps.ResourceID
join v_GS_PATCHSTATEEX PSX on PSX.ResourceID=ps.ResourceID
join v_ApplicableUpdatesSummaryEx summ on
ps.UpdateID=summ.UpdateID
where (ps.LastStateName=@status and summ.ID=@Title) and
(summ.Type = ‘Microsoft Update’) and (summ.product NOT LIKE ‘Windows Server 2003′)
group by Netbios_Name0, user_Domain0,user_Name0,SiteCode,LastHWScan,LastStatusTime,LastStatusMessageIDName,TimeApplied0,LastExecutionResult
order by Netbios_Name0
Click on the “Prompts Button”
Create a new prompt with the following Name: “status”
Give it a prompt text for ex: Select the Status
Provide the following sql statement to the prompt for status with the given syntax
select distinct LastStateName from v_GS_PatchStatusEx
Create another prompt value for Title with the sytax query :
Select Title,ID,Product from v_GS_PatchStatusEx
Create report 2 called Status of Each bulletin ID
declare @n float
select @n = count(distinct ps.ResourceID)
from v_GS_PatchStatusEx ps
join v_FullCollectionMembership fcm on ps.ResourceID=fcm.ResourceID
join v_ApplicableUpdatesSummaryEx summ on
ps.UpdateID=summ.UpdateID
where (ps.ID=@Title or ps.QNumbers=@Title or ps.Title=@Title) and
(summ.Type =’Microsoft update’) and (summ.product NOT LIKE ‘Windows Server 2003′)if IsNULL(@n,0) = 0 return
select @Title as Title, ps.LastStateName, count(distinct ps.ResourceID) as ‘Totals’,
ROUND(100.0 * count(distinct ps.ResourceID)/@n,2) as ‘Percentage %’from v_GS_PatchStatusEx ps
join v_FullCollectionMembership fcm on ps.ResourceID=fcm.ResourceID
join v_ApplicableUpdatesSummaryEx summ on ps.UpdateID=summ.UpdateID
where (ps.ID=@Title or ps.QNumbers=@Title or ps.Title=@Title)
and (summ.Type = ‘Microsoft update’ ) and (summ.product NOT LIKE ‘Windows Server 2003′)
group by ps.LastStateName
Prompt for Title: select Title,ID,QNumbers from v_GS_PatchStatusEx
Once you create the report,just right click on the report and choose properties ,Choose the “Links” tab , Choose link Type: “link to another report“choose the report the one which you created above(report 3 in this case). make sure you have selected the correct columns which are marked in red circle
almost we come to an end by creating last report called 1) Patch Management summary
select summ.ID,summ.QNumbers as ‘Q Number’,
COUNT(distinct ps.ResourceID) as ‘Requested’,
COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End) as ‘Installed’,
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 %’
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 2003′) and (summ.language=’Swedish’)
group by summ.ID,summ.QNumbers
order by summ.ID
If you want to get the information from particular collection,then you can limit the Above report on a specified collection ,here is the one to go.
select summ.ID,summ.QNumbers as ‘Q Number’,
COUNT(distinct ps.ResourceID) as ‘Requested’,
COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End) as ‘Installed’,
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 %’
from v_GS_PatchStatusEx ps
JOIN v_FullCollectionMembership fcm on ps.ResourceID=fcm.ResourceID
join v_ApplicableUpdatesSummaryEx summ on
ps.UpdateID=summ.UpdateID
where (summ.QNumbers=’975562′ or summ.QNumbers=’978695′ or summ.QNumbers=’979482′ or summ.QNumbers=’980195′ or summ.QNumbers=’982381′) and
(summ.Type=’Microsoft Update’) and (summ.product NOT LIKE ‘Windows Server 2003′) and(fcm.CollectionID =@collID)
group by summ.ID,summ.QNumbersorder by summ.ID
You would need to create promot collId given below:
begin
if (@__filterwildcard = ”)
select CollectionID, Name from v_Collection order by Name
else
select CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name
end
The above report will generate status for specific bulletin ID’s for swedish language in brief.If you want to generate report for other languages or you want to get patch status irrespective of Laguage,you can simply delete it.
Once you create this report,right click and select properties.Choose the “Links” tab,Choose link Type: “link to another report” ,choose the report that you have created above(report 2 inthis case).ensure you have the correct columns fields like below otherwise you will mislead the report.
you have done now,reports are ready for you.
Report for Particular Bulletin ID ,click on MS10-007
click on failed status,which gives you all machines
Hope it helps you insome way.The same reports are still work in SCCM in similar way but before doing it SCCM,change the bulletin ID numbers and language(in mycase it is Swedish)
Note: when you copy and paste the quiries to your SMS/SCCM server ,you might see some errors because of copy and paste.All these quiries are present in notepad attached here Status report quiries
All the reports are working well in SCCM environment but you will have to remove a part of syntax called “and (summ.product NOT LIKE ‘Windows Server 2003′)” from the reports which you use since in SCCM,the product value is NULL.If you use the above quiries without modifying,you may see blank report.
May 24th, 2010 at 2:09 PM
Hi,
I tried all the steps as you given the pictures , first two reports are working fine but third reports is not giving any data, its showing me the Records not Found”.
In first table itself its shown me the error for @Title so i declared but while running third report its no data.
Provide me the third report.
Thanks in advance.
Syed
May 24th, 2010 at 4:08 PM
Syed, did u create link to the 2nd report to third report as shown above .Go to the report 2 called Status of Each bulletin ID and link to the third report because,the input for 3rd report which u r looking should comes from 2nd report results… the first column input is ID and 2nd column input is status .
May 25th, 2010 at 10:22 AM
Hi Eskon,
I tried all the steps as shown above the queries and screen shots but while creating the 3rd report its asking the Title variable declartion so i assigned the Title as varchar.
But while excuting the reports am not getting the expected results from 3rd Report( its not showing me the host names and any data).
Looking forward from you.
May 25th, 2010 at 3:42 PM
In 3rd report ,you have @tile and you should declare the prompt value for Title but before it was given ID to declare prompt value.Now it is corrected .you should create prompt value called Title in Both the reports 3 and 2
June 28th, 2010 at 9:26 PM
Here is a post that offers some more surprising stats about Patch management.
http://links.maas360.com/stateOfPatchManagement
November 19th, 2010 at 5:43 PM
Hi Eskon, I created the first report but I am gettting the following error:
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable “@__timezoneoffset”. New to sql, could you please provide me wuth the declaration.
November 19th, 2010 at 6:01 PM
Are you creating these reports in SMS or SCCM Environment ?
For easy made,I have uploaded these 3 reports into single MOF file .below is the path to download
https://docs.google.com/leaf?id=0B7bRoL0yQL84MzUwZWVhNjktY2IzMy00M2Q4LThkYmMtMGZkMjZhMWY3MmZi&hl=en&authkey=CLzwqJ0C
You can download and import it to SMS reports directly.
dont forget to link the reports since,the output of first report is given as input to 2nd report ,same follow to 3rd report as well.
Let me know if you face any issues ?
January 13th, 2011 at 3:38 PM
Hi Eswar ,
your idea for patch report is very nice, can we create patch report on collectionwise deployment report on monthly basis.
means i have 10 collections of sms client and deployed patch on that,how to make report of deployment report.
please suggest.
thanks
irshad
January 17th, 2011 at 12:24 PM
Hi,
Yes,you can create and get the status of the patches for a particular collection.You can edit the 1st report add fcm.CollectionID =@collID to where condition.Create a prompt for thsi to list the collections.
There is report avilable how to apply the report onto collection with heading If you want to get the information from particular collection,then you can limit the Above report on a specified collection ,here is the one to go.
Still if you are unable to create,comment it on this.
March 31st, 2011 at 10:48 AM
Hi,
The below link is not working .
https://docs.google.com/leaf?id=0B7bRoL0yQL84MzUwZWVhNjktY2IzMy00M2Q4LThkYmMtMGZkMjZhMWY3MmZi&hl=en&authkey=CLzwqJ0C
an you send me the MOF file to csemoorthy21@yahoo.com
March 31st, 2011 at 11:56 AM
i can able to download it. Can you try this link https://docs.google.com/leaf?id=0B7bRoL0yQL84MzUwZWVhNjktY2IzMy00M2Q4LThkYmMtMGZkMjZhMWY3MmZi&sort=name&layout=list&num=50
April 5th, 2011 at 8:42 PM
Hi
I have downloaded the MOF file and imported it successfully in SCCM 2007 SP2 R3. Then linked them together.
However when I run the first report I always get nothing – ‘No matching records….’
What am I doing wrong?
Thanks
April 6th, 2011 at 2:02 PM
Did you change the patch number(KB or MSID numbers ) what are you looking for ?
to see if there are any matching records avilable in DB or not ,identify computer and look for resource explorer for checking the patch installation.
April 6th, 2011 at 4:12 PM
OK do you need amend the SQL statement for each and every patch? I just want a report on all patches deployed and their status. Is this not possible? Thanks
April 6th, 2011 at 6:05 PM
I have amended the queries and removed the references to (summ.product NOT LIKE ‘Windows Server 2003′) as we are a Windows 2008 R2 only house and the first 2 reports work.
However when you dive into the 3 report looking for the actual machines for each patch you then receive ‘no records…’. Any ideas? Thanks
April 13th, 2011 at 12:29 PM
you should link the 2nd report to 3re report in such a way that ,3rd report input should be the first column of 2nd report.
check if you have linked the report properly or not.
August 9th, 2011 at 10:25 PM
Eswar,
I’m having no luck running any of these reports and was hoping you could help.
We have a mixed environment of a few server 2000, 2003, 2003 R2, 2008 and 2008 R2….and all updates are in English.
I’d like to be able to see a list of all of my servers, their overall compliancy percentage, how many updates they need, and then see which updates they are missing.
Is this possible?
August 16th, 2011 at 5:18 PM
Yes,you can do that.the posted report gives you information on desktop patches not includes servers.to have that,you will have to remove summ.product NOT LIKE ‘Windows Server 2003 and se if you can get it.
September 15th, 2011 at 7:31 PM
Eswar,
you seem to be the SQL guru we need. I am trying to create a compliance report that looks like this:
System Name Needs reboot Last booted BulletinID
Server A * 2011-05-20 10:46:00.000 MS10-048, MS10-055, MS11-041
Server B 2011-08-19 22:41:00.000 MS11-065
Server C 2011-05-20 10:20:00.000 MS11-065, MS11-071
I do have a working report but this lists multiple lines for a system depending on how many required patches it is missing.
Being green with SQL I am stuck. I believe I need to create a table, but nothing has worked so far.
Any ideas?
September 16th, 2011 at 12:39 AM
That chart does not look good. Let me try again:
System Name Needs reboot Last Booted Bulletin ID
System A 2011-08-20 01:07:00.000 MS11-071, MS11-072, MS11-073, MS11-074
System B * 2011-09-10 10:46:00.000 MS11-071
System C * 2011-08-20 10:04:00.000 MS10-065, MS11-070,
System D 2011-05-20 12:46:00.001 MS11-002
System E * 2011-05-20 10:46:00.002 MS10-066, MS11-003, MS11-074
The “*” would indicate a reboot is pending on that system.
right now I have a report that looks like this:
Name Reboot needed last reboot BulletinID
System A 2011-08-20 01:07:00.000 MS11-071
System A 2011-08-20 01:07:00.000 MS11-072
……. And so on. EACH bulletin causes a new line.
I want Each SYSTEM to be on one line with all buletins listed.
Thanks in advance for any help.
September 16th, 2011 at 3:35 PM
can you post ur query(the report in which u r getting multiple rows with same computer name but different bulletin ID’s).
Did you use group by syntax in ur query ? post looking into ur sql query ,i can help you.
September 16th, 2011 at 6:31 PM
Hi Eswar,
Here is the query statement: (More explanation after)
————————————————————————–
Select Distinct
v_R_System.Name0,
v_GS_SoftwareFile.filename as ‘server group’,
‘Waiting Reboot’ = case when v_UpdateComplianceStatus.LastEnforcementMessageID =’9′ then ‘*’ else ” end,
v_gs_operating_system.lastbootuptime0 as ‘last boot up’,
v_UpdateInfo.BulletinID,
v_UpdateInfo.ArticleID,
–v_UpdateInfo.dateCreated,
v_UpdateInfo.isexpired,
v_UpdateInfo.IsSuperseded,
v_UpdateInfo.Title
from v_updateComplianceStatus
join v_UpdateInfo on v_UpdateInfo.ci_id=v_updateComplianceStatus.ci_id
join v_r_system on v_updateComplianceStatus.resourceID=v_r_system.resourceID
Join v_ClientCollectionMembers on v_ClientCollectionMembers.ResourceID=v_r_system.ResourceID
join v_gs_softwarefile on v_gs_softwarefile.resourceid = v_r_system.resourceid
join v_gs_operating_system on v_gs_operating_system.resourceID = v_R_System.ResourceId
WHERE v_updateComplianceStatus.status=2 and bulletinid like ‘ms%’ and bulletinid != ‘ms10-090′ and v_Gs_SoftwareFile.FileName like ‘SVRGRP%.flg’
GROUP BY v_r_system.Name0, BulletinID,articleID, title, isdeployed, IsSuperseded, v_UpdateInfo.isexpired, v_UpdateComplianceStatus.LastEnforcementMessageID, v_GS_SoftwareFile.filename,v_gs_operating_system.lastbootuptime0
order BY v_GS_SoftwareFile.filename, v_r_system.Name0, BulletinID
—————————————————–
There is more in this statement obviously, as this is what we are using now. This gives one row per update per server.
We have server groups for update patching so I can sort by that later if needed. Management also wants to know if a system is “pending reboot” so I added that column.
I know I can, and want to remove the articleID and Title columns too. This is the original query I started with. I have deleted the others out of frustration.
Thank you
September 19th, 2011 at 5:33 PM
NO MATCHING RECORDS COULD BE FOUND – why?
You might want to NOT annoy people, getting them to create their own reports, only to include a link later for them to download saving them all that time. I say ALL THAT TIME, because none of you code is tagged as code in this web page, so all the quotes are wrong (curly, not straight).
Very very frustrating, and ultimately a complete waste of time because it did not work!
September 20th, 2011 at 12:19 PM
It works for everyone including me in my production environment. If the report says no matching records found,it is not falut with report query rather there are no records that matches from the values given in the query.can you post the query what are you trying for ?
also did you change the order in the linked report as said in the blog since the output of report will be the input of another report so columns must match else you will get no matching records found.
October 7th, 2011 at 12:02 AM
Hi Eswar,
I have cut the query down to the bare necessities. Here is the query:
Select Distinct
v_R_System.Name0,
v_UpdateInfo.BulletinID
from v_updateComplianceStatus
join v_UpdateInfo on v_UpdateInfo.ci_id=v_updateComplianceStatus.ci_id
join v_r_system on v_updateComplianceStatus.resourceID=v_r_system.resourceID
WHERE v_updateComplianceStatus.status=2 and bulletinID like ‘ms%’
group by name0, bulletinid
order by name0
Thanks for any help with this
November 23rd, 2011 at 1:15 AM
i guess you can’t help then??!!??
November 29th, 2011 at 9:24 PM
hi Eswar,
is it possible to get the patch information from the update list? for example we have an Update List named windows servers 2003 and i want that status
Thanks
November 30th, 2011 at 12:07 PM
Yes,you can get the status from update list .try using default reports avilable with catagory software updates–A.Compliance and D.Deployment status
January 19th, 2012 at 9:50 PM
College Textbooks…
[...]Eswar Koneti's Blog » Blog Archive » Monthly Patch statistics reports in SMS/SCCM to show up to the management in a simplified manner[...]…
January 22nd, 2012 at 2:42 PM
Very great post. I simply stumbled upon your blog and wanted to say that I’ve truly enjoyed surfing around your weblog posts. After all I will be subscribing on your feed and I hope you write once more very soon!
March 12th, 2012 at 5:50 PM
Good Going Eswar..A very nice website
July 31st, 2012 at 6:06 PM
I am trying to modify the first report to only show where update id is not blank.
I have tried:
ps.UpdateID!=”
summ.UpdateID!=”
ps.UpdateID like ‘MS-%’
summ.UpdateID like ‘MS-%’
but with no luck.
Also if possible I need to combine all patch numbers for a each specific MS instead of have multiple lines.
For example you first photo shows MS10-013 on 2 lines.
I need them on one line, with the total installed and total required (which would be 5528 and 5462 respectively)
Thanks!
August 6th, 2012 at 7:14 AM
hi,
Are you trying to list all with no UPdate IDs ? ps.UpdateID!=”
what is your requirement ?
August 6th, 2012 at 6:28 PM
I think I got it to work. What I needed was:
MS10-050 10 Patched 20 Missing
MS11-123 1000 Patched 0 Missing
In your example they are first grouped by MS number then by KB number.
But I think I made it work.
August 7th, 2012 at 1:44 PM
Glad u made it work.
August 12th, 2012 at 3:14 AM
could you please sent to me doc with screen shoot. it help me to mote understand
August 13th, 2012 at 6:43 AM
what document are you looking for ? all the information is available on the web.
October 19th, 2012 at 4:54 PM
Hi, We use SCCM 2012…
How can i usee it for sccm 2012 ?
Can u send me pls mof files, i cant download from your links
October 22nd, 2012 at 5:31 PM
you canot use this report in sccm as the views are changed in database not the same as avilable in sms 2003/ sccm 2007.
You have lot default reports avilable which can do similar job but i will try to figure it out if if there is way to use the same in SCCM 2012.
October 31st, 2012 at 5:47 PM
Hi, Thx a lot
February 13th, 2013 at 10:09 AM
Hi,
I am looking for SCCM2012 query for patch status from specific deployment and specific collection. I need the following details in my patch report
Total patch count in deployment
Total patch count installed
Total patch count missing
Total patch count not required
February 14th, 2013 at 7:40 AM
Did you check the default reports ? You should get something to proceed further.I can get back to you on this later once am done with this.
February 14th, 2013 at 6:04 PM
Looking for a SQL query to list down the computer name with patches update success or failure based on the unique deployment id on SCCM 2007
February 19th, 2013 at 10:12 AM
how about default reports ? have you tried them ?