Report Question
I am wanting to build a report that will allow me to have a snapshot look at the patching phase of machines. I have the following query.
SELECT DISTINCT M.NAME, M.IP, M.USER_NAME, KT.PHASE AS PATCHING_PHASE
FROM KBSYS.KONDUCTOR_TASK KT
LEFT JOIN ORG1.MACHINE M ON M.KUID =KT.KUID
WHERE KT.TYPE LIKE 'patch%' and KT.PHASE like 'completed'
ORDER BY M.NAME
Unfortunately, when it runs, it does not pull the most recent data. It pulls historical data rather than current data. Is there a way to get the current data?
SELECT DISTINCT M.NAME, M.IP, M.USER_NAME, KT.PHASE AS PATCHING_PHASE
FROM KBSYS.KONDUCTOR_TASK KT
LEFT JOIN ORG1.MACHINE M ON M.KUID =KT.KUID
WHERE KT.TYPE LIKE 'patch%' and KT.PHASE like 'completed'
ORDER BY M.NAME
Unfortunately, when it runs, it does not pull the most recent data. It pulls historical data rather than current data. Is there a way to get the current data?
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
zookdj
13 years ago
I used this custom report SQL statement and export to an XLS file for easy sorting / filtering. It might have more information than you need, so feel free to customize it as you like. I find this report useful because it just shows me the systems/patches that need attention. The standard KBox reports are too cumbersome for monitoring patching issues, so I eventually came up with this. If you want the XML layout code let me know. (It includes links in the spreadsheet results so that you can go straight to the computer or patch page with a single click.)
SELECT DISTINCT P.ID, DESCRIPTION AS Description, LAST_RUN AS 'Last Run'
, CASE REBOOT_MODE WHEN 0 THEN 'No Reboot' WHEN 1 THEN 'Prompt User' WHEN 2 THEN 'Force Reboot' ELSE 'NA' END AS Reboot
, CASE PATCH_ACTION WHEN 1 THEN 'Detect' WHEN 2 THEN 'Detect & Deploy' WHEN 3 THEN 'Deploy' ELSE 'NA' END AS 'Mode'
, CONCAT(IC.MINUTES, ' ' , IC.HOURS, ' ', IC.DAYS_OF_MONTH, ' ', IC.MONTHS, ' ', IC.DAYS_OF_WEEK) AS 'CRON Schedule'
, MAX_RUN_DURATION / 60 AS 'Suspend'
, K.PHASE AS Phase
, M.NAME AS Machine, M.ID as Machine_ID
, PMS.STATUS AS 'Detect Status', PMS.STATUS_DT AS 'Detect Date', CONCAT( PMS.DEPLOY_STATUS, ' (', CAST(PMS.DEPLOY_ERROR_CODE AS char), ')') AS 'Deploy Status', DEPLOY_ATTEMPT_COUNT AS 'Deploy Tries'
, PP.UID, PP.TITLE AS Patch, PP.IMPACTID, PP.RELEASEDATE AS 'Release Date', User
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_DEPLOY_LABEL_JT PSDL ON P.ID = PSDL.PATCHLINK_SCHEDULE_ID
LEFT JOIN PATCHLINK_PATCH_LABEL_JT PPL ON PSDL.LABEL_ID = PPL.LABEL_ID
LEFT JOIN PATCHLINK_MACHINE_STATUS PMS ON M.ID = PMS.MACHINE_ID AND PPL.PATCHUID = PMS.PATCHUID
LEFT JOIN KBSYS.PATCHLINK_PATCH PP ON PMS.PATCHUID = PP.UID
LEFT JOIN IM_CRON IC ON P.IM_CRON_ID = IC.ID
LEFT JOIN PATCHLINK_PATCH_STATUS PPS ON PMS.PATCHUID = PPS.PATCHUID
WHERE LAST_RUN <> 0
AND DEPLOY_ERROR_CODE != 0
AND PMS.STATUS = 'NOTPATCHED'
AND PPS.STATUS = 0
ORDER BY RELEASEDATE, IMPACTID, Patch
SELECT DISTINCT P.ID, DESCRIPTION AS Description, LAST_RUN AS 'Last Run'
, CASE REBOOT_MODE WHEN 0 THEN 'No Reboot' WHEN 1 THEN 'Prompt User' WHEN 2 THEN 'Force Reboot' ELSE 'NA' END AS Reboot
, CASE PATCH_ACTION WHEN 1 THEN 'Detect' WHEN 2 THEN 'Detect & Deploy' WHEN 3 THEN 'Deploy' ELSE 'NA' END AS 'Mode'
, CONCAT(IC.MINUTES, ' ' , IC.HOURS, ' ', IC.DAYS_OF_MONTH, ' ', IC.MONTHS, ' ', IC.DAYS_OF_WEEK) AS 'CRON Schedule'
, MAX_RUN_DURATION / 60 AS 'Suspend'
, K.PHASE AS Phase
, M.NAME AS Machine, M.ID as Machine_ID
, PMS.STATUS AS 'Detect Status', PMS.STATUS_DT AS 'Detect Date', CONCAT( PMS.DEPLOY_STATUS, ' (', CAST(PMS.DEPLOY_ERROR_CODE AS char), ')') AS 'Deploy Status', DEPLOY_ATTEMPT_COUNT AS 'Deploy Tries'
, PP.UID, PP.TITLE AS Patch, PP.IMPACTID, PP.RELEASEDATE AS 'Release Date', User
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_DEPLOY_LABEL_JT PSDL ON P.ID = PSDL.PATCHLINK_SCHEDULE_ID
LEFT JOIN PATCHLINK_PATCH_LABEL_JT PPL ON PSDL.LABEL_ID = PPL.LABEL_ID
LEFT JOIN PATCHLINK_MACHINE_STATUS PMS ON M.ID = PMS.MACHINE_ID AND PPL.PATCHUID = PMS.PATCHUID
LEFT JOIN KBSYS.PATCHLINK_PATCH PP ON PMS.PATCHUID = PP.UID
LEFT JOIN IM_CRON IC ON P.IM_CRON_ID = IC.ID
LEFT JOIN PATCHLINK_PATCH_STATUS PPS ON PMS.PATCHUID = PPS.PATCHUID
WHERE LAST_RUN <> 0
AND DEPLOY_ERROR_CODE != 0
AND PMS.STATUS = 'NOTPATCHED'
AND PPS.STATUS = 0
ORDER BY RELEASEDATE, IMPACTID, Patch
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
