SQL Statement for Count of Missing Patches on a PC
Kace Reporting: I am working for a simple statement that give me the name of the pc and how many patches are missing on that PC (not the patch names) in SQL.
Example:
Name Missing Patches
Apple 5
Pears 2
Oranges 14
Example:
Name Missing Patches
Apple 5
Pears 2
Oranges 14
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
Here you go:
SELECT MACHINE.NAME, MACHINE.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
ORDER BY UNPATCHED DESC
We use a similar query in a smart label to identify machines that need more than 20 patches installed:
SELECT MACHINE.NAME, MACHINE.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
