/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Help with this SQL

09/19/2018 457 views
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.

Be the first to answer this question

 
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