K1000 Reports - Patching Reports for Completion by Patch, Machine & Vendor using Labels
______________________________________

This is a set of patching completion reports I created in helping a user with this question...
http://www.itninja.com/question/patch-report-for-critical-patches

...and after seeing sklauminzer's excellent blog here (which I redid & tweaked to add label support):
http://www.itninja.com/blog/view/report-of-critical-paths-deployed-by-vendor

This basically takes the completion idea and runs it across several variations, along with the ability to restrict on a label (or labels):

Concise listing of completion rate for all active critical patches by machine.
Detailed listing of completion rate for all active critical patches by machine.
Completion rate for all active critical patches.
Completion rate for all active critical patches grouped by vendor.

Just be aware that as these are doing some major calculations across thousands of patches and joining on multiple tables, they can take several seconds to run.

Hope these help somebody out there!  ^_^

John
______________________________________

Notes:
________________

If you want to change the label being targeted in any of these reports, just change this line:
AND L.NAME = 'server'

So it has your label name (instead of 'server').  If you want to target multiple labels, use this instead:
AND L.NAME rlike 'label1|label2|label3'
________________

To restrict listing to ## days, add this line to the WHERE statements and change the number before DAY.

For example this will restrict to the last 60 days of patches:
AND RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY)
______________________________________
______________________________________

*Title*
Patch listing completion rate by machine - concise (server)

*Category*
Patching - Completion (Custom)

*Description*
Concise listing of completion rate for all active critical patches by machine (servers).

*SQL Query*
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'server'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME
______________________________________

Example Output:

Title: Patch listing completion rate by machine - concise (server)
Description: Concise listing of completion rate for all active critical patches by machine (servers).
Category: Patching - Completion (Custom)
Server Hostname: kbox.company.net
Generated: 2012/10/31 14:20:30

#   Machine Name  Windows Version   Patched  Notpatched  Percent Patched  
 1  pgh-test      Microsoft(R)...       119          40               75  
...
10  gso-file      Microsoft(R)...       167           4               98  
...
28  pgh-net       Microsoft(R)...       170           0              100  
29  pgh-print     Microsoft Wi...       124           0              100  
etc...
______________________________________
______________________________________

*Title*
Patch listing completion rate by machine - detailed (server)

*Category*
Patching - Completion (Custom)

*Description*
Detailed listing of completion rate for all active critical patches by machine (servers).

*SQL Query*
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
PP.TITLE AS PATCH_NAME,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'server'
GROUP BY M.NAME, PATCH_NAME
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE

*Break on Columns*
MACHINE_NAME
______________________________________

Example Output:

Title: Patch listing completion rate by machine - detailed (server)
Description: Detailed listing of completion rate for all active critical patches by machine (servers).
Category: Patching - Completion (Custom)
Server Hostname: kbox.company.net
Generated: 2012/10/31 14:23:35

#     Windows Version  Patch Name         Patched  Notpatched  Percent Patched     Released    
115 of Machine Name: pgh-back
   1  Microsoft®...    MS06-061 MSX...          1           0              100   2006-10-13  
   2  Microsoft®...    MS 921896 Mic...         1           0              100   2007-02-18  
   3  Microsoft®...    MS07-028 Sec...          1           0              100   2007-05-08  
   4  Microsoft®...    MS 942288 Win...         1           0              100   2008-06-02  
etc...
______________________________________
______________________________________

*Title*
Patch listing completion rate by patch (server)

*Category*
Patching - Completion (Custom)

*Description*
Completion rate for all active critical patches (servers).

*SQL Select Statement*
SELECT P.PATCH_NAME, P.PATCHED, P.NOTPATCHED,
P.PERCENT_PATCHED, P.RELEASED
FROM (SELECT PP.TITLE AS PATCH_NAME,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
IFNULL(ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
 +SUM(MS.STATUS='NOTPATCHED')))*100,0), 0) AS PERCENT_PATCHED,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'server'
GROUP BY PP.TITLE) P
WHERE P.PERCENT_PATCHED != 0
ORDER BY P.RELEASED desc, P.PATCH_NAME
______________________________________

Example Output:

Title: Patch listing completion rate by patch (server)
Description: Completion rate for all active critical patches (servers)
Category: Patching - Completion (Custom)
Server Hostname: kbox.company.net
Generated: 2012/10/31 14:26:28

#    Patch Name                 Patched  Notpatched  Percent Patched     Released    
  1  MS12-054 Security Upd...        12           0              100   2012-10-09  
  2  MS12-054 Security Upd...         3           3               50   2012-10-09  
  3  MS12-054 Security Upd...         1           4               20   2012-10-09  
  4  MS12-054 Security Upd...         1           0              100   2012-10-09  
etc...
______________________________________
______________________________________

*Title*
Patch listing completion rate by vendor (server)

*Category*
Patching - Completion (Custom)

*Description*
Completion rate for all active critical patches grouped by vendor (servers).

*SQL Select Statement*
Select PP.VENDOR AS 'Critical Patches by Vendor',
COUNT(CASE WHEN MS.STATUS='PATCHED' THEN MS.MACHINE_ID END) as PATCHED,
COUNT(CASE WHEN MS.STATUS='NOTPATCHED' THEN MS.MACHINE_ID END) as UNPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = "Critical"
AND PP.IS_SUPERCEDED = 0
AND PPS.STATUS = 0
AND L.NAME = 'server'
GROUP BY PP.VENDOR
order by PP.VENDOR
______________________________________

Title: Patch listing completion rate by vendor (server)
Description: Completion rate for all active critical patches grouped by vendor (servers).
Category: Patching - Completion (Custom)
Server Hostname: kbox.company.net
Generated: 2012/10/31 14:37:43

#  Critical Patches By Vendor  Patched  Unpatched  Percent Patched  
1  Adobe Systems, Inc                1          0              100  
2  Microsoft Corp.                4007        170               96