/build/static/layout/Breadcrumb_cap_w.png

KACE Patch Report for Installed Patches past week and Missing Patches for Label

I needed a way to track patches that was more precise than the standard kace shotgun approach. Below are two separate reports.


1. Detect missing patches for a label. (This is a slightly edited version of the default)

SELECT PP.IDENTIFIER,
PP.TITLE,
GROUP_CONCAT(IF((MS.STATUS = 'NOTPATCHED'), M.NAME, NULL)) AS SERVERS_WITHOUT_PATCH,
COUNT(*) AS TOTAL,
SUM(IF((MS.STATUS = 'PATCHED'), 1, 0)) AS PATCHED,
SUM(IF((MS.STATUS = 'NOTPATCHED'), 1, 0)) AS NOTPATCHED,
SUM(IF((MS.DEPLOY_ATTEMPT_COUNT >= MS.MAX_DEPLOY_ATTEMPT 
          and MS.STATUS != 'PATCHED'
        or MS.STATUS = 'FAIL' 
        or (MS.DEPLOY_STATUS = 'FAIL' and MS.STATUS != 'PATCHED')), 1, 0)) AS ERROR
FROM PATCHLINK_MACHINE_STATUS MS
JOIN MACHINE M ON M.ID=MS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE_LABEL_JT ML on ML.MACHINE_ID = M.ID
WHERE ML.LABEL_ID = (select ID from LABEL where NAME = 'LABEL NAME')
-- AND PP.IMPACTID = 'Critical'                       UNCOMMENT THIS LINE TO SHOW CRITICAL PATCHES
-- AND PP.IMPACTID = 'Recommended'          UNCOMMENT THIS LINE TO SHOW RECOMMENDED PATCHES
-- AND PP.IMPACTID = 'Software'                    UNCOMMENT THIS LINE TO SHOW SOFTWARE PATCHES
GROUP BY PP.UID
HAVING NOTPATCHED > 0
ORDER BY PP.TITLE


2. Show patches that were installed on a machine based on a schedule name in the last X days/weeks/months and the status of the installation.


select M.NAME as COMPUTER_NAME, PP.TITLE as PATCH_NAME, PP.IMPACTID as PATCH_IMPACT, MS.DEPLOY_STATUS as PATCH_STATUS, MS.DEPLOY_STATUS_DT as INSTALL_DATE from MACHINE M
left join PATCHLINK_MACHINE_STATUS MS on MS.MACHINE_ID = M.ID
left join KBSYS.PATCHLINK_PATCH PP on PP.UID = MS.PATCHUID
left join PATCHLINK_SCHEDULE PS on PS.ID = MS.SCHEDULE_ID
where MS.DEPLOY_STATUS_DT > subdate(now(), interval 1 day)   CHANGE TO TIME INTERVAL. CAN USE # + (second, minute, hour, week, month, year)
and PS.DESCRIPTION = 'Server Patch Job'    CHANGE TO THE NAME OF YOUR PATCH SCHEDULE NAME OR UNCOMMENT FOR ALL SCHEDULES
ORDER BY M.NAME, PP.TITLE

When creating the SQL Report...
Break on Columns: COMPUTER_NAME

Comments

  • Very helpfull. Thank you! - Hrkljus 5 years ago
  • Is there a reason why #2 would not list all servers associated with that patch schedule? I know the patch schedule installed patches, but for some reason not all the servers show up on my report? - jacob.edwards 4 years ago
    • I as well have this question. I run the report based on a server patch schedule and only a few servers show up with details regarding the last patches applied. The thing is there are more servers in that patch schedule that were patched. Very strange. It seems like a great report though I just wish it would show every server. What could be the cause of this? - jcaine 3 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ