Hello,

Does anyone have a report that will give us list of all devices and the last successful windows patch deploy. We will really appreciate any help we can get.

Thanks,

N

1 Comment   [ + ] Show Comment

Comments

  • Chuck, Thanks for the answer. I am trying to tweak your SQL to get success and failed patch for the past week. I am not very familiar with the KACE schema so any help will be appreciated. Thanks again for your help. Much appreciated
    • If you remove the WHERE PHASE = "completed" it will show all phases (scheduled, reboot snoozed, error, etc.).

      If you remove the WHERE STATUS = "PATCHED" line from the second report it should show you the last status which may be a failure.

      Have you setup a tool like MySQL Workbench to look at the database? There isn't a published schema for the K1000 but it is layed out very well and I generally find it easy to determine relationships.
Please log in to comment

Answers

0
This report finds machines with a completed patching phase, which should be the same as successfully deployed patches:
SELECT MACHINE.ID, MACHINE.NAME, P.DESCRIPTION, PSMS.PATCHED, PSMS.NOTPATCHED, PSMS.DETECT_FAILURES, PSMS.LAST_RUN, K.PHASE, K.TYPE
FROM PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS
JOIN MACHINE on MACHINE.ID = PSMS.MACHINE_ID
JOIN PATCHLINK_SCHEDULE P on P.ID = PSMS.PATCHLINK_SCHEDULE_ID
JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE and K.KUID = MACHINE.KUID
WHERE PHASE = "completed"
ORDER BY MACHINE.NAME
To get the specific last patch that was deployed you can try this:
SELECT M.NAME, PATCH.TITLE, PMS.STATUS, PMS.STATUS_DT, MAX(PMS.DEPLOY_STATUS_DT) AS "Deploy Date"
FROM ORG1.PATCHLINK_MACHINE_STATUS PMS
JOIN MACHINE M on PMS.MACHINE_ID = M.ID
JOIN KBSYS.PATCHLINK_PATCH PATCH on PATCH.UID = PMS.PATCHUID
WHERE STATUS = "PATCHED"
GROUP BY M.NAME

Answered 11/16/2015 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity