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.QNumbers
order 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.
