Custom KACE Patch Report to Count Number of Machines Patched Instead of Listing Machine Names
08/22/2014 4674 views
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)