query to retreive job status? - Community

Kev (5 posts)

March 20, 2014 12:48 PM

Accepted Answer

I am using adTempus 3.x and I would like to get the job info via query for a report.

When I use the query below and I look at the status in any of the tables it is either a weird string or some integer value. Is there a way to get the status as the client does, Failed, Aborted, or Succeed etc..?

I was hoping to avoid the API since I am more comfortable in SQL. Plus I don't have time to learn the API.

select job.name, jobgroup.name , executionHistoryItem.executionstart, executionHistoryItem.executionfinish From job, jobgroup, executionHistoryItem Where job.jobgroup = jobgroup.oid And job.oid = executionHistoryItem.joboid and job.jobgroup = jobgroup.oid and job.name = 'MyJobName'

J.D. Staff (46 posts)

March 20, 2014 01:07 PM

Accepted Answer

You just need to add a join to the StatusStringsEN view:

select job.name, jobgroup.name , executionHistoryItem.executionstart, executionHistoryItem.executionfinish, s.stringText as Status
From executionHistoryItem join job on executionhistoryitem.joboid=job.oid
join jobgroup on job.jobgroup=jobgroup.oid
join StatusStringsEN on executionhistoryitem.status=StatusStringsEN.stringID
Where job.name = 'MyJobName'

Kev (5 posts)

March 20, 2014 01:12 PM

Accepted Answer

Wow, Thanks. Perfect. I am writing a monitor so if the job status is Failed it can send me an alert.

Maybe a feature for future adTempus client.

J.D. Staff (46 posts)

March 20, 2014 01:18 PM

Accepted Answer

adTempus already does e-mail notification for failed jobs, if that's what you mean. You need a Response with a Notification Action. There is an example in the version 3 docs and the version 4 docs.

Kev (5 posts)

March 20, 2014 01:51 PM

Accepted Answer

Didn't know that. That will work for most of the jobs. However, I am also monitoing the run time.

So if the job status is Running and it has been running for >20 minutes from ExecutionStart then I also want an alert because the job maybe hung. Doesn't seem to be a way to do that in Responses since the current state is Running and a condition will not be met.

J.D. Staff (46 posts)

March 20, 2014 02:11 PM

Accepted Answer

If you create the Response at the Step level there is an Event available for "Task runs longer than the specified time" (I just noticed it's not in the docs). This will probably do what you want.

There is no corresponding option at the job level in version 3 (but there is in version 4), so you would need to add this to each Step that you are worried about.

Kev (5 posts)

March 20, 2014 02:18 PM

Accepted Answer

Excellent. When I came on this job we already used this software. But other than basic job creation none of our staff really knew the advanced features. Guess I will have to spend a day reading the docs. Thanks so much for the help!!

PS Notifications work great with outlook groups. :-D

J.D. Staff (46 posts)

March 20, 2014 02:53 PM

Accepted Answer

Glad to help. Enjoy the read... :)