Replacement query for Job Steps Execution History by Ibrahim Naji

JobAgent_SQLJobAgentScheduleTimeline_dataset.sql

SELECT
	name
	-- added Days Back to use a fitler aginst the user input
	,DATEDIFF(DAY,CONVERT(DATE, calc.run_date),GETDATE()) AS DaysBack
	,CONVERT( DATETIME, calc.run_date + SPACE(1) + calc.run_time) AS start_time
	-- using seconds here as I had a problem with times over 24 hrs
	,DATEADD(SECOND, jh.run_duration, CONVERT( DATETIME, calc.run_date + SPACE(1) + calc.run_time)) AS end_time
	,run_status
	,instance_id
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
-- you where using TIME before so i'm assuming SQL 2008+
CROSS APPLY (
	SELECT	
		-- using brittle padding, we know the source is a number 
		-- stuffing in symbols from back to front just a preference
		STUFF(STUFF(REPLACE(STR(jh.run_date, 8), SPACE(1), '0'),7,0,'-'),5,0,'-') AS run_date,
		STUFF(STUFF(REPLACE(STR(jh.run_time,6),SPACE(1),'0') ,5,0,':'),3,0,':') AS run_time,
		STUFF(STUFF(REPLACE(STR(jh.run_duration,6),SPACE(1),'0'),5,0,':'),3,0,':') AS run_duration
) AS calc
WHERE
	step_id = 0 
	-- limit query to only last week and remove dynamic filter, I have a small setup
	AND jh.run_date >= CONVERT(INT, FORMAT(DATEADD(DAY, -7, GETDATE()), 'yyyyMMdd', 'en-GB')) 
	-- just using a magic number here for simplicity, 05 minutes 00 seconds
	AND jh.run_duration > 500 
ORDER BY
	start_time