/build/static/layout/Breadcrumb_cap_w.png

Smart label (SQL query) to detect computers with > 10 missing windows updates

Ideally I'd like to get the count that shows up under Inventory->Devices->DeviceName->Security and have a smart label created based off of it.  It doesn't appear that an existing filter exists for this, or at least I cannot find one under advanced searches...

That being said, I've began going through the MySQL database, but I'm having a hard time finding which tables I should be pulling this data from in the database.  PATCHLINK_MACHINE_STATUS looks promising, but I'm getting weird numbers.  On a computer that shows 15 pending updates in the Kace, this SQL query returns 26...

SELECT 
 MACHINE_ID, COUNT(STATUS), MACHINE.NAME, MACHINE.ID
FROM 
 PATCHLINK_MACHINE_STATUS
  JOIN MACHINE ON MACHINE_ID = MACHINE.ID
WHERE
 STATUS LIKE 'NOTPATCHED'
GROUP BY
 MACHINE_ID


Has anybody created something like this before?  Some direction would be nice.

I've tried searching ITNinja and came up with no results, but last time I posted here somebody was able to pull up some results when I couldn't, so if you're a better ITNinja searcher than me, existing articles outlining this would be much appreciated as well!

2 Comments   [ + ] Show comments
  • I love the idea of this. Unfortunately I can't figure out how to build a smart label from SQL code. Is it done from the Smart Label area or somewhere else?
    I am pretty new to Kace, so forgive my ignorance. - bvanalstine 8 years ago
    • No worries, we were all new once! You'll want to go to Inventory->Computers, click on "Smart Label". At the bottom of that window you'll see "Choose Label". In that box type in what you want your label to be called (perhaps 'Missing >10 Windows Updates') and hit save. (Don't worry about the actual search terms above, we're going to clear them out and add in the SQL in the next step.)

      Once you have a smart label, go into Home->Label Management->Smart Labels and click on the label you just created. Then you have the option to "Edit SQL". Remove what's in there and put in this SQL code :)

      Note: There may be easier/better ways to do this, but this is how I've always done it and it creates the manual label and applies the matching smart label to it, so that's why I do it this way. - matt1738 8 years ago
  • Thank you so much! - bvanalstine 8 years ago

Answers (1)

Answer Summary:
Posted by: chucksteel 8 years ago
Red Belt
2

Top Answer

This was a fun one to figure out. The PATCHLINK_MACHINE_STATUS table includes entries for patches that are superceded, so you need to filter those out by linking to the KBSYS.PATCHLINK_PATCH table. Here is the SQL code that I came up with that works as 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 > 10



Comments:
  • Ahhhh I knew I was close, but totally missing something! I did not even consider the superseded patches! Thank you very much, I hope this query is helpful to other people in the future! - matt1738 8 years ago

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

 
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