Report: Patches That Are In A Label And What Detect And Deploy Schedules They Are Part Of

This report shows all the patches that you have assigned to a label (including filter labels) and what detect and / or deploy schedule they are a part of. It will also show if that schedule is active (on) or not (off).

Note: Put the following in the Break on columns field: Label, PatchName, PatchStatus

select L.NAME Label, PP.TITLE PatchName,
case when PPS.STATUS=0 THEN 'Active'
when PPS.STATUS=1 THEN 'Inactive'
when PPS.STATUS=4 THEN 'Disabled'
ELSE PPS.STATUS END PatchStatus,
GROUP_CONCAT(DISTINCT CONCAT(PS_DEPLOY.DESCRIPTION, IF(PS_DEPLOY.ENABLED=1,' (on)',' (off)')) SEPARATOR ' ::: ') DeploySchedues,
GROUP_CONCAT(DISTINCT CONCAT(PS_DETECT.DESCRIPTION, IF(PS_DETECT.ENABLED=1,' (on)',' (off)')) SEPARATOR ' ::: ') DetectSchedule

from PATCHLINK_PATCH_LABEL_JT PPLJ JOIN LABEL L ON L.ID=PPLJ.LABEL_ID
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID=PPLJ.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID=PP.UID
LEFT JOIN (
SELECT IM_CRON.ENABLED,LABEL_ID,DESCRIPTION,DEPLOY_ALL_PATCHES FROM PATCHLINK_SCHEDULE_DEPLOY_LABEL_JT PSDLJ
RIGHT JOIN PATCHLINK_SCHEDULE PS
ON PATCHLINK_SCHEDULE_ID=PS.ID
JOIN IM_CRON ON IM_CRON_ID=IM_CRON.ID
WHERE
PATCH_ACTION IN (2,3)
) PS_DEPLOY
ON PS_DEPLOY.LABEL_ID=L.ID OR PS_DEPLOY.DEPLOY_ALL_PATCHES=1

LEFT JOIN (
SELECT IM_CRON.ENABLED,LABEL_ID,DESCRIPTION,DETECT_ALL_PATCHES FROM PATCHLINK_SCHEDULE_DETECT_LABEL_JT PSDLJ
RIGHT JOIN PATCHLINK_SCHEDULE PS
ON PATCHLINK_SCHEDULE_ID=PS.ID
JOIN IM_CRON ON IM_CRON_ID=IM_CRON.ID
WHERE
PATCH_ACTION IN (1)
) PS_DETECT ON PS_DETECT.LABEL_ID=L.ID OR PS_DETECT.DETECT_ALL_PATCHES=1
WHERE NAME NOT LIKE 'HDN_%'
GROUP BY PP.TITLE
ORDER BY L.NAME, PPS.STATUS