Kace - Help with Schedule Status Report
Hello,
I was looking to generate a report that basically duplicates the schedule deploy status (Called "Patch Tasks") that's found at the bottom of the schedule setup screen (ie... see the image below). I found a built in report that I modified simply to include the name of the schedule I want to view, however it doesn't display the exact same list as the image below. I determined that the SQL statement of the report doesn't include systems that have Patch Results as "Unavaliable".
My question:
Is there a report that basically duplicates what is seen in the image below, or can anyone assist in getting my SQL code to also display systems that are Unavailable just like the image. Either way would be a huge help :-). I've pasted my SQL code below the image. Thanks
select M.NAME as NAME, 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 DATE, PSS.DESCRIPTION as Schedule
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 KT.TYPE = PSS.KONDUCTOR_TASK_TYPE and PSS.DESCRIPTION = 'PM - WORKSTATIONS (ACTIVE SYSTEMS)'
order by M.NAME
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
h2opolo25
9 years ago
select M.NAME as NAME, 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 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 = 'Monday Detect Job'
and KT.TYPE = PSS.KONDUCTOR_TASK_TYPE
Change PSS.DESCRIPTION as needed for your schedule or remove the line to show all.
Comments:
-
Thanks so much... that does give me more detail that I do need and matches up the columns shown on the screen, however I'm still missing any row with the Status "Not Scheduled" (As you see in the screen shot I gave, "Not Scheduled" details lines do appear there, but they won't appear in my report. Any way around this?
Thanks Again. - jdrouillard 9 years ago-
Did you ever get an answer on this? - jrkeister26 6 years ago