February 5, 2014 at 7:00 am
Hi, we have numerous scripts in place to track job failures etc. But I've noticed a few times that our alerting is not picking up a specific scenario.
This is when a step fails (ie from Step_id 1 onwards), and therefore reports a run_status of 0 in Sysjobhistory. But the overall outcome of the job itself ie Step 0 does not fail ie may issue a success message or warning, and is NOT a run_status of 0 in Sysjobhistory.
So does anyone have code to pick this condition up please. I basicaly want to select from sysjobhistory where the run_status is 0 (failed) for failed steps ie anything but Step_id 0, but only where the same jobs Step_id of 0 (ie overal job outcome) doesn't have a run_status of 0.
I already get alerts for jobs that fail and don't want to double up on the alerts I get, hence this specific code.
Hope that make sense, thanks
February 5, 2014 at 8:08 am
If I understood correctly,I think something like this would be what you are looking for. I didnt have any jobs in this state so i just created a test job that has a step that fails and the failure condition was to quit the job reporting success. This gave the job step (step 2) a run status of 0 and step 0 a run status of 1.
SELECT a.step_id,a.step_name,a.run_status,b.step_id,b.step_name,b.run_status
FROM sysjobhistory AS a
INNER JOIN sysjobhistory AS b
ON a.job_id = b.job_id
AND a.run_date = b.run_date
AND a.run_time = b.run_time
WHERE a.step_id = 0
AND a.run_status <> 0
AND b.step_id > 0
AND b.run_status = 0;
February 5, 2014 at 8:56 am
Thanks for that Bob. The only slight issue being that the overall job run_time doesn't always equal the step run_time ie the step can be 1min + longer.
February 5, 2014 at 10:08 am
If it's only a couple of seconds that is the issue and you don't have jobs that run multiple time a minute you could zero out the seconds in the run_time column.
SELECT a.step_id,a.step_name,a.run_status,b.step_id,b.step_name,b.run_status
FROM sysjobhistory AS a
INNER JOIN sysjobhistory AS b
ON a.job_id = b.job_id
AND a.run_date = b.run_date
AND round(a.run_time,-2) = round(b.run_time,-2)
WHERE a.step_id = 0
AND a.run_status <> 0
AND b.step_id > 0
AND b.run_status = 0;
February 6, 2014 at 1:59 am
Yes I'm sure that'll be fine, thanks for your help Bob 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply