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...
MACHINE_ID, COUNT(STATUS), MACHINE.NAME, MACHINE.ID
JOIN MACHINE ON MACHINE_ID = MACHINE.ID
STATUS LIKE 'NOTPATCHED'
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!
Please log in to answer
Posted by: chucksteel 5 years ago
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