Custom KACE Patch Report to Count Number of Machines Patched Instead of Listing Machine Names
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
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
9 years ago
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.
Comments:
-
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! - ajacob 9 years ago-
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 - chucksteel 9 years ago
-
This was working great for me...until i upgraded to v10. I took a stab in the dark at correcting table names but no success. Can someone help? I currently don't have the spare cycles to fix my workbench to verify the tables and columns. Thank you! - k3HIUSA 4 years ago
