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