Master of None

Wanted to share this script. I pieced it together some time ago from various scripts. I think this might be the source for at least some of the query.

Removing the step_ID from the where clause will show run times by step_id and Total RunTime (step_id 0).

USE master
GO
SELECT SERVERPROPERTY('ServerName') [ServerName/Instance]
,job_name
,CASE WHEN step_id = 0 then 'Total Run Time' else CAST(step_id as varchar(10))End as step_id
,step_name
,datename(DW,run_datetime)StartDate
,run_datetime
,run_datetime + CASE WHEN run_duration = '00:00:0*' then '00:00:00'
ELSE run_duration END [run_end_time]
,CASE WHEN run_duration = '00:00:0*' then '00:00:00'
ELSE run_duration END run_duration
,Run_status
FROM
(
SELECT job_name,step_id,step_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
,CASE WHEN run_status = 0 then 'Failed'
WHEN run_status =1 then 'Succeeded'
WHEN run_status =2 then 'Retry'
WHEN run_status =3 then 'Canceled'
WHEN run_status =4 then 'In progress'
ELSE 'Unknown'
END run_status
,[Results]
FROM
(
SELECT DISTINCT
j.name as job_name, step_name,step_id,run_date as rd,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
,run_status
,h.message [Results]
FROM msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
) t
) t
WHERE
--step_id <> '0' AND -- Show step completion times
step_id = 0 AND -- Show only the run times
--(step_id = 3 or step_id =2) AND -- Show specific step
run_datetime >CONVERT(varchar(10),GetDate()-1,121) ---Yesterday & Today
ORDER BY run_datetime, job_name, step_id 

Tiny URL for this post:
 Copy