TONYSO

Tonyso Credo: "Look for a pattern, and solve it for everything"

Blogs

How Microsoft IT Does Patch Reporting

  • Comments 2
  • Likes

The screen shot below shows an example of a production report for Microsoft IT patching, clipped from this PPT from IT 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

    Comments
    Your comment has been posted.   Close
    Thank you, your comment requires moderation so it may take a while to appear.   Close
    Leave a Comment