Query to detect unpatched machines
Does anyone happen to have a query that will return a list of un-patched computers. I'm looking basically to recreate the results that are generated in the K1000 when you click, 'Security >> Patch Management >> Schedules >> Click a schedule. At the bottom it shows the machines and their current patch status.
I've been tasked with creating a report identifying machines that show errors, failures, or status other than 'completed'
Patch Tasks
| Name | IP Address | Status | Patch Results | Date |
|---|---|---|---|---|
| AUS-WIN73201 | 192.1.1.2 | error (Log Upload Failed) | Patched: 0, Not Patched: 0, Detect Failures: 0 , Deploy Failures: 0 | 09/20/2014 02:10:34 |
| ASQLCOMP1 | 192.168.1.13 | error (Log Upload Failed) | Patched: 0, Not Patched: 0, Detect Failures: 0 , Deploy Failures: 0 | 09/20/2014 02:10:33 |
| CRONUTS | 192.168.1.14 | completed | Patched: 132, Not Patched: 0, Detect Failures: 0 , Deploy Failures: 0 | 09/20/2014 02:12:00 |
| DC567J11 | 192.168.1.15 | error (Log Upload Failed) | Patched: 0, Not Patched: 0, Detect Failures: 0 , Deploy Failures: 0 | 09/20/2014 02:10:34 |
2 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
cblake
6 years ago
Something like this maybe?
#PATCH RESULT SUMMARY BY DEVICE
SELECT
M.NAME AS NAME,
M.IP AS 'Last Known IP',
M.USER_FULLNAME AS 'Last User Logged On',
KT.PHASE AS STATUS,
PS.PATCHED as 'Patched',
PS.NOTPATCHED as 'Not Patched',
PS.DETECT_FAILURES as 'Detect Failures',
PS.LAST_RUN AS 'Last Patch Attempt (Time)',
M.LAST_INVENTORY AS 'Last seen by KACE (Time)',
DATE(M.LAST_REBOOT) AS 'Last Reboot (Date)'
FROM
MACHINE M
LEFT JOIN
PATCHLINK_SCHEDULE_MACHINE_STATUS PS ON PS.MACHINE_ID = M.ID
LEFT JOIN
PATCHLINK_SCHEDULE PSS ON PSS.ID = PS.PATCHLINK_SCHEDULE_ID
LEFT JOIN
KBSYS.KONDUCTOR_TASK KT ON KT.KUID = M.KUID
WHERE
KT.TYPE = PSS.KONDUCTOR_TASK_TYPE
and KT.PHASE != 'COMPLETED'
ORDER BY STATUS
Comments:
-
I have little knowledge of writing SQL queries. Is there a way to add the WHERE P.DESCRIPTION= 'patch schedule name' to this query? I'd like to be able to separate the report based on the patch schedule name OR the patch label name. - edullum 5 years ago
-
You might consider reaching out to Professional Services to scope some 1-1 time for this and other tasks/projects you're working on. We can do work for you, or guide you and learn along the way, or do pure training. A lot of options exist (including custom options). https://support.quest.com/kace-systems-management-appliance/professional-services - cblake 5 years ago
I'm really new to this, but if I understood how the tables joined and which tables contained the patch information I could probably muddle through it, but the I can't really find anything documenting the tables. - rmavery 10 years ago