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




65h95M.png

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

Comments

Please log in to comment

Answers

0
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.
Answered 12/10/2014 by: h2opolo25
Red Belt

  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity