Setting job event trigger for dynamic timings

[Bill Heilman]

We are currently running ver 6.1.0.594 and I am looking for some assistance/suggestions on alerts.  I know that Job Events can be set against jobs running longer than maximum time or longer than expected.  Being in the CPG business, our job run times can fluctuate based on what quarter we are in, so jobs running in Q3 & Q4 can have a tendency to run longer.  Is, or has, anyone found a way to have the alert trigger if the runtime has exceeded the estimated runtime by X minutes?  (i.e.  a job is estimated to run for 90 minutes, I want to be able to trigger an alert if the job runs 15 minutes past estimated.)   Maybe if not something that has a 'workaround' at this point, maybe a thought for an enhancement?

[Derrick Au]

Hi Bill,

Runtime has exceeded the estimated runtime by X minutes, this can only be done by making a direct change to the job definition. Perhaps not ideally, but either triggers for job running longer than expected (based on estimated duration) or job running longer than its maximum time window (based on max duration) should work in your case. No matter which quarter you are in, you can establish a max threshold knowing that job(s) should not run for more than, for example, 105 minutes.

Another way is a customised solution by creating a SQL query to run against Admiral database. Depending on which quarter you are in, check to see if job is active AND jobrun_time + jobrun_duration > jobrun_estduration + X minutes, then send out an alert notification.

BR,

Derrick Au

[Bill Heilman]

Hi Derrick,

Thanks for the update!  Unfortunately, with hundreds of jobs that could be potentially critical, updating each job definition is pretty much out of the question.  And along the same lines, it seems we'd need to create a SQL query for each of the jobs times 4 (each quarter), which also would be pretty time consuming to set up and manage.  Thanks though.

Bill

[Derrick Au]

Hi Bill,

It's much simpler. This would involve writing a single SQL query. Create an external table that contains a listing of all your jobs you want monitored, and join that with the Admiral database jobrun table to extrapolate the information you need. So that you would have only one external table to update, and for each job  record you can modify the estimated times and X minutes in overage to all your heart's content.

BR,

Derrick Au