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?

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
This is the query I use for my "Needs Patches" smart label:
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

Answered 08/11/2016 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share