if exists (select * from dbo.sysobjects where id = object_id(N'dbo.usp_get_job_status') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.usp_get_job_status
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure dbo.usp_get_job_status
as
begin
select 
  j.job_id
, j.name as job_name
, case when j.enabled = 1 then 'Enabled' else 'Disabled' end as job_enabled 
, j.description as job_description
, j.start_step_id 
, j.date_created as job_created
, j.date_modified as job_modified
, case j.notify_level_eventlog
    when 0 then 'never'
    when 1 then 'on success'
    when 2 then 'on fail'
    when 3 then 'on completion'
    else 'unknown'
  end as eventlog_notification
, case j.notify_level_email
    when 0 then 'never'
    when 1 then 'on success'
    when 2 then 'on fail'
    when 3 then 'on completion'
    else 'unknown'
  end as email_notification
, case j.notify_level_netsend
    when 0 then 'never'
    when 1 then 'on success'
    when 2 then 'on fail'
    when 3 then 'on completion'
    else 'unknown'
  end as net_send_notification
, case j.notify_level_page
    when 0 then 'never'
    when 1 then 'on success'
    when 2 then 'on fail'
    when 3 then 'on completion'
    else 'unknown'
  end as pager_notification
, s.step_id
, s.step_name
, s.command as job_step_command
, s.on_success_step_id as step_ok_next_step_id
, case s.on_success_action 
    when 1 then 'quit w/success'
    when 2 then 'quit w/fail'
    when 3 then 'goto next step'
    when 4 then 'goto step'
    else 'unknown'
  end as step_success_action
, s.on_fail_step_id as step_fail_next_step_id
, case s.on_fail_action 
    when 1 then 'quit w/success'
    when 2 then 'quit w/fail'
    when 3 then 'goto next step'
    when 4 then 'goto step'
    else 'unknown'
  end as step_fail_action
, s.database_name as step_database_name
, coalesce(s.output_file_name, N'no output file in use') as step_out_filename
, case s.last_run_outcome 
    when 0 then 'failed'
    when 1 then 'succeeded'
    when 3 then 'canceled'
    else 'unknown'
  end as last_step_run_outcome
, s.last_run_duration as last_step_run_duration_seconds
, s.last_run_date as last_step_run_date
, s.last_run_time as last_step_run_time
, h.message
 from msdb..sysjobs j with (nolock)

inner join msdb..sysjobsteps s with (nolock)
on j.job_id = s.job_id

inner join msdb..sysjobhistory h with (nolock)
on j.job_id = h.job_id
and s.step_id = h.step_id

inner join
( 
select 
  job_id
, step_id
, year(most_recent_run_date) * 10000 + 
  month(most_recent_run_date) * 100 +
  day(most_recent_run_date) as latest_run_date
, datepart(hh, most_recent_run_date) * 10000 +
  datepart(mi, most_recent_run_date) * 100 +
  datepart(ss, most_recent_run_date) as latest_run_time
from 
(
select 
  job_id
, step_id
, cast(max(
      left(cast(run_date as varchar(8)),4) + '-' +
      substring(cast(run_date as varchar(8)),5,2) + '-' +
      right(cast(run_date as varchar(8)),2) + ' ' +
      left(right('000000' + rtrim(cast(run_time as varchar(6))), 6),2) + ':' +
      substring(right('000000' + rtrim(cast(run_time as varchar(6))), 6),3,2) + ':' +
      right('000000' + rtrim(cast(run_time as varchar(6))), 2)
     ) as datetime) as most_recent_run_date

from msdb..sysjobhistory with (nolock)
group by job_id, step_id
) a
) mh
on j.job_id = mh.job_id
and s.step_id = mh.step_id
and h.run_date = mh.latest_run_date
and h.run_time = mh.latest_run_time

order by j.job_name, s.step_id
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/*
Don McMunn is a professional, business-focused consultant specializing in data warehousing and business intelligence design, development and delivery for over a decade with the Fortune 500 and SMB segments.  He may be reached at dmcmunn at ipcdesigns dot com 
*/