02-04-2015 08:21 AM - edited 03-01-2019 09:12 AM
I have monitoring job(for eg. A) that is going to verify if any of its dependency jobs(for eg. B) have executed successfully or not. So if job(B) that is there in dependency fails or times out anything, Can the failed dependency name and status be displayed in Job's output(A)?
02-04-2015 10:32 AM
Hi abhangal,
It is possible. Let's say monitoring job A is set up to run every X minutes with a new occurrence. Then also set up string variables job B name, and job B status. Create a bat file, monitoring.bat, to echo $1 $2. So in Job A, command is monitoring.bat and parameters will just be variables job B name and job B status. When job B fails or times out, then a variable action (define within B itself) will update variables job B name and job B status. Next run of job A will show name and status of job B. Problem with this approach is you'd have to create variables for each children job and status, and then repeat for next set jobs to be monitored.
or how about creating a monitoring job A that executes a sqlcmd to query Admiral for the job run status? The below will return everything within past hour, and the results can be filtered to show completed abnormally, timedout, etc.
SQL query:
SELECT
dbo.jobrun.jobrun_proddt as [PROD DATE],
dbo.jobmst.jobmst_prntname as [PARENT GROUP],
dbo.jobmst.jobmst_name as [JOB NAME],
dbo.jobrun.jobrun_reruns as [JOB RERUNS],
dbo.jobrun.jobrun_time as [START TIME],
[End Time] = DATEADD(s,dbo.jobrun.jobrun_duration,dbo.jobrun.jobrun_time),
dbo.jobrun.jobrun_duration as [DURATION (s)],
CASE dbo.jobrun.jobrun_status
WHEN '1' THEN 'JOB_STATUS_WAIT'
WHEN '3' THEN 'JOB_STATUS_HOLD'
WHEN '51' THEN 'JOB_STATUS_ACTIVE'
WHEN '52' THEN 'JOB_STATUS_STOP'
WHEN '53' THEN 'JOB_STATUS_DEFERRED'
WHEN '66' THEN 'JOB_STATUS_ERROR'
WHEN '101' THEN 'JOB_STATUS_NORMAL'
WHEN '103' THEN 'JOB_STATUS_ABNORMAL'
WHEN '104' THEN 'JOB_STATUS_SKIPPED'
WHEN '105' THEN 'JOB_STATUS_ORPHAN'
WHEN '106' THEN 'JOB_STATUS_ABORTED'
WHEN '108' THEN 'JOB_STATUS_TIMEOUT'
WHEN '109' THEN 'JOB_STATUS_CANCELLED'
END AS [STATUS]
FROM
dbo.jobmst INNER JOIN
dbo.jobrun ON dbo.jobmst.jobmst_id = dbo.jobrun.jobmst_id
WHERE
dbo.jobrun.jobrun_time BETWEEN DATEADD(hour,-1,GETDATE()) AND GETDATE()
02-04-2015 01:04 PM
Hi,
Thanks for your response. Running SQL query looks quite favorable to me.
But I have few questions.
1. how can I output the results of query to Output window.
2. If result output that some job has completed abnormally can i set up event based upon the result of query; for eg. sending out notification or set this current job that is running is query to abnormal so that alert can be sent with output.
02-05-2015 08:48 AM
Hi abhangal,
sqlcmd results can be displayed on console, or contents can be redirected into a text/csv file. But let's go with the first option, then in your job definition, you can select scan on normal or abnormal strings and specify strings.
For example,
scan for abnormal strings = "Completed Abnormally"
job event on completed abnormally, and email action to send out the alert ... joboutput would be from this monitoring job and not from the actual failed job. Hmmm...you could try to store the output from job B into a variable, and then have monitoring job publish value.
It's easier to just tie job event on completed abnormally, and email action to send out the alert directly to the job themselves. So that when job B fails, an email gets sent out with the output information. And probably better to keep the monitoring job separate for monitoring/reporting purposes. Let me know if any of these options work for you.
BR,
Derrick Au
02-06-2015 10:45 AM
Hi Derrick Au,
Thanks for your suggestions. I am currently working around it set up jobs. I will update you if any issue encountered.
But can I ask you one question, if I want learn more about Tidal's Admiral database can you point me to the learning source or database mapping. Asking this because the sql query you gave is really helpful so really want to play around it.
Thanks,
-abhangal
02-06-2015 12:01 PM
Hi abhangal,
A copy of the Admiral data model can be found on,
BR,
Derrick Au
02-06-2015 01:47 PM
02-06-2015 02:19 PM
Abhangal,
Drag file to desktop and it should open from there.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide