/build/static/layout/Breadcrumb_cap_w.png
11/03/2016 1197 views
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


0 Comments   [ + ] Show comments

Comments


All Answers

1
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


Answered 11/04/2016 by: chucksteel
Red Belt