The screen shot below shows an example of a production report for Microsoft IT patching, clipped from this PPT from IT Showcase.
Here is the sample report query for compliance:
Select ID, sum(case when LastStateName='Install Verified‘ then 0 else 1 end) as Requested,
sum(case when LastStateName='Install Verified' then 1 else 0 end) as Installed,
100 * sum(case when LastStateName='Install Verified' then 1 else 0 end)/count(*) as '% Compliant'
From v_GS_PatchStatusEx
Where LocaleID in (0,2052,1028,1033,1036,1031,1040,1041,1042,1034) and
ID in ('MS05-038', 'MS05-039', 'MS05-040', 'MS05-041', 'MS05-042', 'MS05-043')
Group by ID Having count(*) > 10 Order by ID
Sample report query for SMS installation activity:
Select ID, count (*) Totals from v_gs_patchstatusex
where LocaleID in (0,2052,1028,1033,1036,1031,1040,1041,1042,1034) and laststatename = 'install verified‘ and
id in ('MS05-038', 'MS05-039', 'MS05-040','MS05-041', 'MS05-042') and AgentInstallDate is not null
Group by ID order by ID
Sample report query for individual patch status:
select laststatename Status, count(*) Totals, convert(decimal(5,2),
((count(*) * 100.0) / (select count(*) from
v_gs_patchstatusex
where
id='ms06-007' and LocaleID<1000))) as Percentage
from v_GS_PatchStatusEx
id='ms06-007'
and LocaleID <1000
group by laststatename
order by Totals Desc
(the LocaleID<1000 part helps to distinguish between ITMU and non-ITMU patches)
Sample report query for the extended errors:
Select ID, Qnumbers,
sum(case when lastexecutionresult= '61686' then 1 else 0 end) as '61686 - Invalid_switch',
sum(case when lastexecutionresult= '61957' then 1 else 0 end) as '61957 - STATUS_UPDATE_ALREADY_RUNNING',
sum(case when lastexecutionresult= '-532459699' then 1 else 0 end) as '532459699 - Yet to figure out',
sum(case when lastexecutionresult= '3' then 1 else 0 end) as '3 - cant find path',
sum(case when lastexecutionresult= '5' then 1 else 0 end) as '5 - Access Denied',
sum(case when lastexecutionresult= '32' then 1 else 0 end) as '32 - Cannot access the file',
sum(case when lastexecutionresult= '53' then 1 else 0 end) as '53 - network path not found',
sum(case when lastexecutionresult= '59' then 1 else 0 end) as '59 - Unexpected network error occurred',
sum(case when lastexecutionresult= '64' then 1 else 0 end) as '64 - Network name not available',
sum(case when lastexecutionresult= '121' then 1 else 0 end) as '121 - Timeout period expired',
sum(case when lastexecutionresult= '1231' then 1 else 0 end) as '1231 - Network error',
sum(case when lastexecutionresult= '1236' then 1 else 0 end) as '1236 - Network connection was aborted',
sum(case when lastexecutionresult= '1398' then 1 else 0 end) as '1398 - Time/date diff between client and server',
sum(case when lastexecutionresult= '1603' then 1 else 0 end) as '1603 - Fatal Error',
sum(case when lastexecutionresult= '61441' then 1 else 0 end) as '61441 - Incorrect language',
sum(case when lastexecutionresult= '61442' then 1 else 0 end) as '61442 - Status_Checked_Free_Mismatch',
sum(case when lastexecutionresult= '1' then 1 else 0 end) as '1 - Incorrect function',
sum(case when lastexecutionresult= '6' then 1 else 0 end) as '6 - Invalid handle',
sum(case when lastexecutionresult= '1619' then 1 else 0 end) as '1619 - Package open error',
sum(case when lastexecutionresult= '999' then 1 else 0 end) as '999 - Inpage Error',
sum(case when lastexecutionresult= '2' then 1 else 0 end) as '2 - File not found',
sum(case when lastexecutionresult= '61443' then 1 else 0 end) as '61443 - Not enough disk space',
sum(case when lastexecutionresult= '112' then 1 else 0 end) as '112 - Not enough disk space',
sum(case when lastexecutionresult= '61483' then 1 else 0 end) as '61483 - No diskspace',
sum(case when lastexecutionresult= '-1073741818' then 1 else 0 end) as '-1073741818 - Unkownn Error',
sum(case when lastexecutionresult= '536870920' then 1 else 0 end) as '536870920 - IDS_NO_PERMS',
sum(case when lastexecutionresult= '-2147023728' then 1 else 0 end) as '-2147023728 - Unknown wmi error',
sum(case when lastexecutionresult= '-2147483645' then 1 else 0 end) as '-2147483645 - I do not know',
sum(case when lastexecutionresult= '62212' then 1 else 0 end) as '62212 - Watson related issue',
sum(case when lastexecutionresult= '61470' then 1 else 0 end) as '61470 - Catalog install failed',
sum(case when lastexecutionresult= '61473' then 1 else 0 end) as '61473 - Update/Winver mismatch',
sum(case when lastexecutionresult= '193' then 1 else 0 end) as '193 - Invalid Win32 application',
sum(case when lastexecutionresult= '61454' then 1 else 0 end) as '61454 - Platform Mismatch',
sum(case when lastexecutionresult= '61558' then 1 else 0 end) as '61558 - SP level mismatch',
sum(case when lastexecutionresult= '61658' then 1 else 0 end) as '61658 - Failed checking update.inf trust',
sum(case when lastexecutionresult= '61673' then 1 else 0 end) as '61673 - SP Level ?',
sum(case when lastexecutionresult= '62210' then 1 else 0 end) as '62210 - str_dw_main_intro_bold',
sum(case when lastexecutionresult= '536870913' then 1 else 0 end) as '536870913 - I dont know',
sum(1) as 'total errors'
from v_gs_patchstatusex
where ( (ID in ('ms05-038','ms05-039','ms05-040','ms05-041','ms05-042','ms05-043'))
or title like '5-25%' ) and laststatename = 'Failed'
group by ID, Qnumbers order by id
More more information see the the preso IT Showcase: How Microsoft IT Does Patch Management:
Main presentation – sign in required to the live presentation, or the powerpoint slides, or the offline viewing download
WMA – blogcast MP3 – blogcast
PowerPoint slidedeck direct download
Some very nice work on displaying patch status from SMS 2003. If you don&rsquo;t have something in place
PingBack from http://systemcenter.wordpress.com/2006/09/16/tonyso-how-microsoft-it-does-patch-reporting/