/build/static/layout/Breadcrumb_cap_w.png

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?


2 Comments   [ + ] Show comments
  • I am trying to do something similar but I want to target a smart label. My SQL looks like this which works well as a report but doesn't work when I add it as a smart label. The smart label ID is 398. However when I add it to smart label, it will list computers that aren't in the smart label ID 398.

    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
    • Just to be clear, you are trying to create a smart label that will get applied to computers that are missing patches or do you want report on machines that are in a specific label that are also missing patches? - chucksteel 6 years ago
  • Hello Chuck,
    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
    • SELECT MACHINE.NAME AS SYSTEM_NAME, 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
      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

Answers (1)

Posted by: chucksteel 7 years ago
Red Belt
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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ