Report based on Job Schedule
I would like to generate a report that contains the information based on job schedule without the detail of each computer. Basically with this information
2 Comments
[ + ] Show comments
-
I can't really see the image (it's very blurry), but it looks like a patching schedule, is that correct? Do you want a report that shows the number of computers in each state of a patching schedule? - chucksteel 6 years ago
-
yes it is a patching schedule. If you could see the image, it shows number of successful, number of failed, etc etc and total percentage. I want a report that shows just that summarized information. Not detail with every machine. - kspencer@fmh.org 6 years ago
Answers (2)
Please log in to answer
Posted by:
DaveMT
6 years ago
This is the report I use. Do note that you will need to change the PSS.SECSCRIPTION to your Schedule name. Hopefully it will guide you into the direction you are looking for.
select M.NAME as NAME, SYSTEM_DESCRIPTION as Description, M.IP as IP_ADDRESS, KT.PHASE as STATUS, Concat('Patched: ', PS.PATCHED,' , ','Not Patched: ', PS.NOTPATCHED,' , ','Detect Failures: ', PS.DETECT_FAILURES) as PATCH_RESULTS, PS.LAST_RUN as LAST_RUN_DATE
from MACHINE M
left join PATCHLINK_SCHEDULE_MACHINE_STATUS PS on PS.MACHINE_ID = M.ID
left join PATCHLINK_SCHEDULE PSS on PSS.ID = PS.PATCHLINK_SCHEDULE_ID
left join KBSYS.KONDUCTOR_TASK KT on KT.KUID = M.KUID
where PSS.DESCRIPTION = 'PATCHSCHEDULENAME'
and KT.TYPE = PSS.KONDUCTOR_TASK_TYPE
Posted by:
chucksteel
6 years ago
Here is what I came up with:
SELECT DESCRIPTION, LAST_RUN,
PENDING, DOWNLOADING, EXECUTING,
REBOOTING, PAUSED, SUCCEEDED,
FAILED, COMPLETED, OFFLINE
FROM ORG1.PATCHLINK_SCHEDULE_RUN_COUNTS
JOIN PATCHLINK_SCHEDULE on PATCHLINK_SCHEDULE.ID = PATCHLINK_SCHEDULE_RUN_COUNTS.PATCHLINK_SCHEDULE_ID
WHERE PATCHLINK_SCHEDULE.DESCRIPTION = "Patch Production Deploy"
and PATCHLINK_SCHEDULE_RUN_COUNTS.ID =
(SELECT MAX(PATCHLINK_SCHEDULE_RUN_ID)
FROM PATCHLINK_SCHEDULE_RUN_COUNTS
WHERE PATCHLINK_SCHEDULE_ID = PATCHLINK_SCHEDULE.ID)
Be sure to change "Patch Production Deploy" to your schedule's name.
