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)
Please log in to answer
Posted by:
chucksteel
7 years ago
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
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
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