Security

Here's a report that gets almost everything from the view in KACE. The only thing that I'm not sure how to get is the number of patch failures:

SELECT DISTINCT M.NAME AS Machine, 

M.IP, DESCRIPTION AS "Patch Schedule", 

P.LAST_RUN AS 'Last Run' , 

K.PHASE AS Phase , 

PSMS.PATCHED, 

PSMS.NOTPATCHED,

PSMS.DETECT_FAILURES

FROM PATCHLINK_SCHEDULE P 

LEFT JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE 

LEFT JOIN MACHINE M ON K.KUID = M.KUID 

LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS ON PSMS.MACHINE_ID = M.ID

WHERE P.LAST_RUN <> 0 

AND DESCRIPTION not like '%Detect'ORDER BY M.NAME

Also note that this won't detect any schedules that end with the work "Detect". We run separate detection and deployment schedules so this report only returns results for the actual patching schedules.