Last sucessfull patch deploy report
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
9 years ago
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
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. - chucksteel 9 years ago