Hello there!

This is my first time to post on the forum, mainly to ask for help regarding a report I am trying to put together. Our client wants to have a report summarising the following information:

  • Names of patches deployed after the last patching cycle (or within the last one or two weeks);
  • Success and failure count for each patch; and
  • Number of machines not rebooted after last patching;
We need to have this query targeted to use a particular filter combination of patch label and domain (e.g. run this query against set of machines under this patch label on this domain). I have been able to splice together a query using another script I found here on IT Ninja that works but it produces quite a lengthy spreadsheet that is hard to filter on Excel as it lists machine names under each disparate patch names a number of times instead of counting them.

Can anyone help me modifying this current script so it only shows the following:

Patch Name, Identifier, Patched, Not Patched, Requires Reboot

Any assistance is appreciated.

---Here's what we are currently using that works---
Breaks on 'DISPLAY_NAME'

SELECT PP.TITLE AS DISPLAY_NAME
     , M.NAME AS 'Computer Name'
     , M.USER_LOGGED AS 'User Logged'
     , CS_DOMAIN AS Domain
     , PP.RELEASEDATE AS 'Release Date'
FROM
  ORG1.PATCHLINK_MACHINE_STATUS MS
INNER JOIN KBSYS.PATCHLINK_PATCH PP
ON PP.UID = MS.PATCHUID
INNER JOIN ORG1.MACHINE M
ON M.ID = MS.MACHINE_ID
INNER JOIN ORG1.PATCHLINK_PATCH_LABEL_JT
ON PATCHLINK_PATCH_LABEL_JT.PATCHUID = PP.UID
INNER JOIN ORG1.LABEL
ON PATCHLINK_PATCH_LABEL_JT.LABEL_ID = LABEL.ID
WHERE
  MS.STATUS = 'PATCHED'
  AND LABEL.NAME = 'Patch Label Name'
  AND CS_DOMAIN = 'Client Domain'
  AND PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 1 MONTH)
ORDER BY
  DISPLAY_NAME, PP.RELEASEDATE

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
This report shows most of what you are looking for:
SELECT PP.TITLE AS DISPLAY_NAME
     , SUM(if(MS.STATUS = 'PATCHED', 1, 0)) AS 'Patched'
, SUM(if(MS.STATUS = 'NOTPATCHED', 1, 0)) AS 'Not Patched'
FROM
  ORG1.PATCHLINK_MACHINE_STATUS MS
INNER JOIN KBSYS.PATCHLINK_PATCH PP
ON PP.UID = MS.PATCHUID
INNER JOIN ORG1.MACHINE M
ON M.ID = MS.MACHINE_ID
INNER JOIN ORG1.PATCHLINK_PATCH_LABEL_JT
ON PATCHLINK_PATCH_LABEL_JT.PATCHUID = PP.UID
INNER JOIN ORG1.LABEL
ON PATCHLINK_PATCH_LABEL_JT.LABEL_ID = LABEL.ID
WHERE
  PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 1 MONTH)
  AND LABEL.NAME = 'Patch Label Name'
  AND CS_DOMAIN = 'Client Domain'
GROUP BY
  DISPLAY_NAME
ORDER BY
  DISPLAY_NAME, PP.RELEASEDATE

This displays the patches and how many machines were patched and not patched. I wasn't sure what you mean by Identifier so I didn't include that column.

For a report showing machines that still need to reboot you can use this Patch Phase Report that I wrote. It shows each machine with their patching schedule and what phase they are in of patching.
SELECT DISTINCT P.ID, DESCRIPTION AS Description, P.LAST_RUN AS 'Last Run' 
, K.PHASE AS Phase 
, M.NAME AS Machine, M.ID as Machine_ID 
, PSMS.PATCHED, PSMS.NOTPATCHED
FROM PATCHLINK_SCHEDULE P 
LEFT JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE 
LEFT JOIN MACHINE M ON K.KUID = M.KUID 
LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS ON PSMS.MACHINE_ID = M.ID
WHERE P.LAST_RUN <> 0 
AND DESCRIPTION not like '%Detect'
ORDER BY M.NAME

Note that we have separate detection and deployment schedules so this report excludes the Detection schedules.
Answered 08/25/2014 by: chucksteel
Red Belt

  • Hi chucksteel,

    Thanks I plugged in your modified and added our details and it worked. The second one did not though as it threw an error.

    I have a follow up question on both scripts though if you do not mind.

    On the first one, how do I query the date the patch was applied instead of the release date of the patch? Also is it possible to place an actual date range rather than 1 months interval from now?

    For the second script, can this be modified to target a particular machine label and list the status of all the machines in it?

    Looking forward to your response.

    Thanks!
    • In order to determine when patches were deployed you would have to look at the PATCHLINK_MACHINE_STATUS table. The STATUS_DT column paired with a value of "PATCHED" in the STATUS column should indicate when a particular machine had a patch installed, or at least when KACE detected it as being installed.

      To use a date range change this line:
      PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 1 MONTH)
      to
      PP.RELEASEDATE between '2014-07-01' and '2014-08-31'
      with the dates you need in that format.

      I re-worked the second query and here's an updated version that should work. It includes the ability to limit by a specific label.
      SELECT MACHINE.ID, MACHINE.NAME, P.DESCRIPTION, PSMS.PATCHED, PSMS.NOTPATCHED, PSMS.DETECT_FAILURES, PSMS.LAST_RUN, K.PHASE, K.TYPE
      FROM PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS
      JOIN MACHINE on MACHINE.ID = PSMS.MACHINE_ID
      JOIN PATCHLINK_SCHEDULE P on P.ID = PSMS.PATCHLINK_SCHEDULE_ID
      JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE and K.KUID = MACHINE.KUID
      JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
      JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
      WHERE
      LABEL.NAME = "Label Name"
      ORDER BY MACHINE.NAME
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja
Nine Simple (but Critical) Tips for Effective Patch Management
This paper reviews nine simple tips that can make patch management simpler, more effective and less expensive.

Share