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?
Answers (1)
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
SELECT M.NAME AS MACHINE_NAME, M.OS_NAME AS WINDOWS_VERSION,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED
FROM MACHINE_LABEL_JT ML
JOIN MACHINE M ON (M.ID = ML.MACHINE_ID)
JOIN PATCHLINK_MACHINE_STATUS MS ON (MS.MACHINE_ID = ML.MACHINE_ID)
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
where LABEL_ID = 398
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
GROUP BY M.NAME - aquiles 6 years ago
I am trying to create a smart label that target specific machines that are in a label (label ID 398) that are missing critical patches. - aquiles 6 years ago
JOIN KBSYS.PATCHLINK_PATCH PP on PP.UID = PMS.PATCHUID
JOIN MACHINE on MACHINE.ID = PMS.MACHINE_ID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
WHERE PMS.STATUS = "NOTPATCHED"
and PP.IS_SUPERCEDED = 0
and PP.IMPACTID = "Critical"
and LABEL_ID = 398
GROUP BY MACHINE.NAME
HAVING UNPATCHED > 0 - chucksteel 6 years ago