Status of a job via a active schedule (JSCH) object

[Douglas Darbyshire]

I'm looking for best approach and example of accessing the information of all tasks in a executed Jsch object.   Specifically the status.   Our object is to provide a list and possible start jobs that were missed due to a outage.   I'm think a sql query of AE might be best way.   The other might be via API queries.   So any examples would be very appreciated. 

[Daryl Brown]

Here's something we wrote for this type of thing; this should pull up anything skipped over the last 6 hours:  (this is a SQL server query)

select ah_client as client,ah_name as schedule,cast(eh_status as varchar) + ' (ACTIVE)' as sched_status,ejpp_object as skipped_task,
    dateadd(mi,datediff(mi,getutcdate(),getdate()),EJPP_STARTTIME) as start_time,
    dateadd(mi,datediff(mi,getutcdate(),getdate()),EJPP_ENDTIME) as end_time
from ah,ejpp,eh
where ah_otype='JSCH'
and ejpp_status=1941
and ah_idnr=ejpp_ah_idnr
and eh_otype='JSCH'
and eh_ah_idnr=ah_idnr
and eh_status<>1563
and ejpp_starttime > dateadd(hh,-6,getdate())
union all
select ah_client as client,ah_name as schedule,cast(ah_status as varchar) + ' (ended)' as sched_status, ajpp_object as skipped_task,
    dateadd(mi,datediff(mi,getutcdate(),getdate()),AJPP_STARTTIME) as start_time,
    dateadd(mi,datediff(mi,getutcdate(),getdate()),AJPP_ENDTIME) as end_time
from ah, ajpp
where ah_otype='JSCH'
and ah_idnr in (select ah_idnr from ah,eh where ah_otype='JSCH' and eh_otype='JSCH' 
    and ah_name=eh_name and ah_client=eh_client and eh_status<>1563 and ah_timestamp4 > dateadd(hh,-6,getdate()))
and ah_name not in (select ah_name from ah)
and ah_idnr=ajpp_ah_idnr
and ajpp_status=1941
and ajpp_starttime > dateadd(hh,-6,getdate())
ORDER BY 1,2,5,4

We have that implemented as a SQL var; you could build a job to loop through the results and activate the tasks if needed.  (We preferred to review the list manually rather than automatically starting everything.)

Note that this query will only pull up tasks that were skipped from a schedule object.

You'll need a different query to look at skipped events and period containers, if those are of concern.