February 27, 2012 at 8:18 am
Hi,
I have written a query to use in a stored proc to obtain the Job Step history of individual steps within every Agent job that I have setup. I thought I had the everything working fine until I noticed a flaw in my design.
I tried to use the 'job_outcome' column in msdb.dbo.sysjobsteps to determine if a job failed, succeeded, or was canceled until I noticed that some steps that never were executed were coming back as 'Failed. I realize now that I have to look at the 'last_run_duration' column as well, but I am not sure how to re-work my query to include that. Should I try a different approach or could someone suggest how I could rework the following to resolve the issue?
selectconvert(varchar(75), j.name) as [JobName]
, s.step_id
, convert(varchar(75), s.step_name) as [StepName]
, case s.last_run_outcome
when 1 then 'Success'
when 0 then 'Failed'
when 3 then 'Cancelled' end as [StepStatus]
, h.message
, max(s.last_run_date) as last_run_date
, max(s.last_run_time) as last_run_time
, MAX(s.last_run_duration) as last_run_duration
, max(ja.next_scheduled_run_date) as next_run
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s on j.job_id = s.job_id
left join msdb.dbo.sysjobhistory h on s.job_id = h.job_id
and s.step_id = h.step_id
and s.last_run_date = h.run_date
and s.last_run_time = h.run_time
left join msdb.dbo.sysjobactivity ja on s.job_id = ja.job_id
where j.enabled = 1
group by j.name, s.step_id, s.step_name, s.last_run_outcome, h.message
order by j.name, s.step_id
February 27, 2012 at 10:14 am
Think I figured it out...I'll just use 2 case statements instead....seems to work:
, case when MAX(s.last_run_date) > 0 then
case s.last_run_outcome
when 1 then 'Success'
when 0 then 'Failed'
when 3 then 'Cancelled' end
else 'Never Ran' end as [StepStatus]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply