/build/static/layout/Breadcrumb_cap_w.png
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

Comments

  • That looks good to me. Our server group doesn't manage their systems in KACE, so I can't actually test it, though.
  • Worked well for me. And if I changed not like to like, it showed only servers.

There are no answers at this time