/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Last sucessfull patch deploy report

11/13/2015 1264 views

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.

All 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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ