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
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
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 7 years ago
I am pretty new to Kace, so forgive my ignorance. - bvanalstine 7 years ago
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 7 years ago