We recently had a brief outage (we inadvertently rebooted the AE server running the PWP before we could stop it cleanly), so I've now been tasked with figuring out how to identify the tasks we've missed.
I've come up with this query to identify the skipped workflows from the past hour from schedules that weren't themselves stopped at the time (status 1563)...
select ah_client as client,ah_name as schedule,eh_status as sched_status,ejpp_object as skipped_task,
substr(varchar_format(EJPP_ERLSTSTTIMET + CURRENT TIMEZONE,'MM-DD-YYYY HH24:MI'),1,20) as sched_time,
substr(varchar_format(EJPP_STARTTIME + CURRENT TIMEZONE,'MM-DD-YYYY HH24:MI:SS'),1,24) as start_time,
substr(varchar_format(EJPP_ENDTIME + CURRENT TIMEZONE,'MM-DD-YYYY HH24:MI:SS'),1,24) as end_time
and (ejpp_starttime + current timezone) > current timestamp - 1 hour
ORDER BY 1,2,5;
...but I think this leaves out cases where the outage spanned the turnaround time of any schedules. In these cases -- assuming the schedules did their turnaround properly once the AE came back up -- you'd be left with some "ENDED_TIMEOUT - Start time exceeded" tasks in the previous instance of that schedule. This means I'd need to be querying the AJPP table instead of the EJPP table, but I have yet to find a good way to build such a query that doesn't take forever to run.
Anyone have a good solution for this?