K1000 Report for patches deployed during December 2014 and the completion percentage?
I was requested to supply a report of all patches deployed in December 2014 and the percentage of completion for those patches, and I'm trying to figure out how to create a report for this. We deploy patches once a month during maintenance against patch labels and machine labels. The K1000 I'm working with only contains Windows Servers as clients, and is running version 6.2.109330.
I found the "Patch listing completion rate by patch (server)" report from http://www.itninja.com/blog/view/k1000-reports-patching-reports-for-completion-by-patch-machine-vendor-using-labels that outputs the patches, their patched/not patched counts, a completion percentage, and the release date of the patch. I like the format (and most of the contents) of that report, but I'd like to modify that to remove the release date of the patch and rather include the deploy date to be between 12/1/2014 and 1/1/2015. I believe I need to use the S.DEPLOY_STATUS_DT field in a SQL statement to achieve this, but I'm not sure how to format the date range or even how to use that field in a SQL statement. I'd also like to not limit it to "critical" patches as this report is set to do, but all patches that were deployed during that time.Any advice on how to achieve this goal?
Answers (1)
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 - indigoeye1 9 years ago