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?

2 Comments   [ + ] Show Comments

Comments

  • The SQL statement for the report noted above that I would like to change is:

    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
  • Well, that formatting didn't turn out well.
Please log in to comment

Answers

0
http://www.itninja.com/blog/view/kace-patch-report-for-installed-patches-past-week-and-missing-patches-for-label
Answered 01/10/2015 by: h2opolo25
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share