Help with this SQL
I do not have very much SQL experience but I am getting there. :)
Trying to create a smart label that will attach to desktop/laptops (not servers) that are missing more than 20 patches. This works to show devices missing the patches (found it on here in another older post from last year):
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
I feel like I need to add OS_NAME not like '%Server%' somewhere but not sure where. I have tried a couple things but it throws error telling me my sql is incorrect. Any ideas.
EDIT - I think I answered my own question. Does this look correct?
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 and OS_NAME not like '%Server%' GROUP BY MACHINE.NAME
HAVING UNPATCHED > 20
2 Comments
[ + ] Show comments
-
That looks good to me. Our server group doesn't manage their systems in KACE, so I can't actually test it, though. - chucksteel 5 years ago
-
Worked well for me. And if I changed not like to like, it showed only servers. - five. 5 years ago
Answers (0)
Please log in to answer
Be the first to answer this question
