How to set up a smart label for machines missing large numbers of patches?
I have a report that shows me the number of critical patches that each machine is missing. The SQL is this:
Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, COUNT(DISTINCT PP.TITLE) AS NOT_PATCHED, group_concat(distinct concat(PP.TITLE,' ---- (',IDENTIFIER,') ]') order by PP.TITLE separator '\n') AS PATCH_NAME
FROM ORG1.PATCHLINK_MACHINE_STATUS MS JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
where PP.IMPACTID = ('Critical') AND MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */
group by MACHINE_NAME
order by NOT_PATCHED DESC, MACHINE_NAME ASC, PP.TITLE ASC
I'd like to set up a label for the machines where COUNT(DISTINCT PP.TITLE) AS NOT_PATCHED comes back above a certain threshold. I've tried a few ideas of my own, but so far no luck. Has anyone else managed to do something like this?
SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.ID as TOPIC_ID, COUNT(PMS.PATCHUID) as UNPATCHED FROM ORG1.PATCHLINK_MACHINE_STATUS PMS
JOIN KBSYS.PATCHLINK_PATCH PP on PP.UID = PMS.PATCHUID
JOIN MACHINE on MACHINE.ID = PMS.MACHINE_ID
WHERE PMS.STATUS = "NOTPATCHED"
and PP.IS_SUPERCEDED = 0
GROUP BY MACHINE.NAME
HAVING UNPATCHED > 20