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