title: False Negative: Job Step History
body: <p>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.</p>
  
  <p>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 case statement in the following to resolve the issue?</p>
  
  <pre><code>    select  convert(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
  </code></pre>