/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


How to create device smart label from a patch smart label

04/21/2017 2022 views
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

Comments

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


Community Chosen Answer

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

Answered 04/24/2017 by: chucksteel
Red Belt

  • 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

All Answers

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
Answered 04/24/2017 by: my_ninja
White Belt

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