Custom KACE Patch Report to Count Number of Machines Patched Instead of Listing Machine Names
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'
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
MS.STATUS = 'PATCHED'
AND LABEL.NAME = 'Patch Label Name'
AND CS_DOMAIN = 'Client Domain'
AND PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 1 MONTH)