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

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
    • 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?
  • 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.
    • 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
Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

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