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!
Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

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.
    • 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.
  • Thank you so much!
Please log in to comment

Answer Chosen by the Author

2
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


Answered 05/11/2016 by: chucksteel
Red Belt

  • 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!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity