Typical disclaimer: I *really* need to stress that everything below is based on my own research and findings - the information below does not indicate official documentation or support, however some of this may be accurate.

I developed a "CIO level" patching report this morning and am sharing it, in case it might be useful to any other kboxers out there.  ^_^

Hope that helps!

John

___________________________________

*Title*
Vendor severity with machine count and completion rates

*Description*
Lists all active patches by vendor severity, with an applicable machine count, total number patched, total number unpatched and percent patched for each severity level. Keep in mind is that not all patches apply to all machines, so the machine count will vary for each severity level.

*Select Statement*

SELECT (IF(V.ATTRVALUE <> '', V.ATTRVALUE, 'Not Available')) AS VENDOR_RATING,
ROUND((SUM(MS.STATUS='PATCHED')/COUNT(MS.MACHINE_ID)) * 100, 2) AS COMPLIANCE,
COUNT(MS.MACHINE_ID) AS APPLICABLE,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM((MS.DEPLOY_ATTEMPT_COUNT >= 3 and MS.STATUS != 'PATCHED')
OR MS.STATUS = 'FAIL' or MS.DEPLOY_STATUS = 'FAIL') AS ERROR
FROM KBSYS.PATCHLINK_PATCH P
LEFT JOIN KBSYS.PATCHLINK_VENDORATTRIBUTE V ON V.PATCHUID = P.UID
LEFT JOIN PATCHLINK_MACHINE_STATUS MS ON MS.PATCHUID = P.UID
JOIN PATCHLINK_PATCH_STATUS PS ON PS.PATCHUID = P.UID
WHERE V.ATTR = 'MaximumSeverityRating'
AND V.ATTRVALUE not rlike '8211|recommended'
AND PS.STATUS = 0
GROUP BY VENDOR_RATING

 *Example Output*