/build/static/layout/Breadcrumb_cap_w.png

How to create device smart label from a patch smart label

So we are trying to capture devices that are on the network and are missing old patches. This number changes every day, every hour depending on who is signing on, what new machines are being imaged etc, so a manual label will not work. We current have a smart label created to capture all active patches that are marked as missing on devices and released not within the last 30 days, and if you open that patch label, it shows how many devices fall in to that category. How to we get a label applied to those devices. Essentially how do you apply a patch smart label to a device smart label.

0 Comments   [ + ] Show comments

Answers (2)

Posted by: chucksteel 7 years ago
Red Belt
1
I don't think that this is exactly what you are looking to find, but it might help. This report will find machines that are missing more than 20 patches.
SELECT MACHINE.NAME, MACHINE.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


Comments:
  • And this is the SQL used for a 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 - chucksteel 7 years ago
Posted by: my_ninja 7 years ago
White Belt
0
I came here to ask the same thing. I have created a smart label based off the SQL that I use for reporting. This only applies to active Microsoft patches. 
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
AND PPS.STATUS in (0)
AND PP.VENDOR='Microsoft Corp.'
ORDER BY M.NAME, PP.TITLE
 
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