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'
|Name||IP Address||Status||Patch Results||Date|
|AUS-WIN73201||22.214.171.124||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|
Please log in to answer
Posted by: cblake 2 years ago
Something like this maybe?
#PATCH RESULT SUMMARY BY DEVICE
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)'
PATCHLINK_SCHEDULE_MACHINE_STATUS PS ON PS.MACHINE_ID = M.ID
PATCHLINK_SCHEDULE PSS ON PSS.ID = PS.PATCHLINK_SCHEDULE_ID
KBSYS.KONDUCTOR_TASK KT ON KT.KUID = M.KUID
KT.TYPE = PSS.KONDUCTOR_TASK_TYPE
and KT.PHASE != 'COMPLETED'
ORDER BY STATUS