Some very nice work on displaying patch status from SMS 2003.  If you don’t have something in place for your IT director this is a great place to start.

Original source:

How Microsoft IT Does Patch Reporting


The screen shot below shows an example of a production report for Microsoft IT patching, clipped from this PPT fromIT Showcase.

It is from the Microsoft IT main hierarchy a couple of weeks after Patch Tuesday. It uses an SMS 2003 Web dashboard, derived from the queries below.
The upper-left report gives a quick overview of all the updates and their compliance. The following three reports give a breakdown for each update to see what is preventing 100 percent compliance. As usual, pending restarts (from users who used Windows Update or similar methods but did not restart) are by far the biggest issue, and that will reduce as the computers are restarted. The SMS Installs report indicates how SMS installed updates (as opposed to checking compliance). The final report is the error details.
By combining the reports in one dashboard, Microsoft IT has a quick starting point for all key patching indicators.


•Although Microsoft IT has about 180,000 clients in its main hierarchy, the report shows only 130,000 clients (72 percent) because Microsoft has many mobile and test computers that are often not on the network for weeks. In addition, there are some minor exclusions for particular client types.
•SMS installations are low (around 20,000) because the data is overwritten over time. Earlier in the cycle, the percentage was about 70 percent. This part of the report is most useful early in the cycle.
•Update-specific reports are not included for MS05-040, 041, and 042, so that the report can fit on the slide.
•The Extended Errors report is particularly valuable in that it enables investigation of any issues. Any cells in this report that have large numbers, and particularly that are known to not be environmental issues, are worth investigating closely (beginning with the largest number).

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
where
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

tonyso : How Microsoft IT Does Patch Reporting.

The Daily Ramblings of an SMS Engineer Listen to this article No Comments 07:54 - Sep 16, 2006



Trackbacks

No Trackbacks

Comments

No Comments