IF OBJECT_ID('tempdb..#temp_xp_sqlagent_enum_jobs') IS NOT NULL BEGIN
	DROP TABLE #temp_xp_sqlagent_enum_jobs;
END

CREATE TABLE #temp_xp_sqlagent_enum_jobs
	(
		job_id UNIQUEIDENTIFIER NOT NULL
		,last_run_date INT NOT NULL
		,last_run_time INT NOT NULL
		,next_run_date INT NOT NULL
		,next_run_time INT NOT NULL
		,next_run_schedule_id INT NOT NULL
		,requested_to_run INT NOT NULL
		,request_source INT NOT NULL
		,request_source_id NVARCHAR(256) NULL 
		,running INT NOT NULL
		,current_step INT NOT NULL
		,current_retry_attempt INT NOT NULL
		,job_state INT NOT NULL
	)

INSERT INTO #temp_xp_sqlagent_enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ''

SELECT
	CAST(SJ.name AS VARCHAR(256)) AS Name
	,CAST(SJ.[enabled] AS CHAR(1)) AS IsEnabled
	,ISNULL(SUSER_SNAME(SJ.owner_sid), '') AS JobOwner
	,COUNT(DISTINCT SH.instance_id) AS No_Runs24H
	,SUM(CASE WHEN SH.run_status IN (0,3) THEN 1 ELSE 0 END) AS No_Errors24H
	,msdb.dbo.agent_datetime(SH_Latest.run_date, SH_Latest.run_time) AS LastRun
	,DATEADD(SS, (SH_Latest.run_duration/10000*3600) + ((SH_Latest.run_duration/100)%100*60) + (SH_Latest.run_duration%100), msdb.dbo.agent_datetime(SH_Latest.run_date, SH_Latest.run_time)) AS LastEnd
	,CASE SH_Latest.run_status
		WHEN 0 THEN 'Failed'
		WHEN 1 THEN ' Succeeded'
		WHEN 2 THEN ' Retry'
		WHEN 3 THEN ' Canceled'
	END AS LastStatus
	,MIN(SA.next_scheduled_run_date) AS NextRun
	,CASE WHEN SJ.notify_level_eventlog = 1 THEN ' | LogToWindows' ELSE '' END +
		CASE WHEN SJ.notify_level_email = 1 THEN ' | Email ' + SO_Email.name ELSE '' END +
		CASE WHEN SJ.notify_level_netsend = 1 THEN ' | Netsend ' + SO_Netsend.name ELSE '' END +
		CASE WHEN SJ.notify_level_page = 1 THEN ' | Page ' + SO_Page.name ELSE '' END
	AS SuccessAction
	,CASE WHEN SJ.notify_level_eventlog = 2 THEN ' | LogToWindows' ELSE '' END +
		CASE WHEN SJ.notify_level_email = 2 THEN ' | Email ' + SO_Email.name ELSE '' END +
		CASE WHEN SJ.notify_level_netsend = 2 THEN ' | Netsend ' + SO_Netsend.name ELSE '' END +
		CASE WHEN SJ.notify_level_page = 2 THEN ' | Page ' + SO_Page.name ELSE '' END
	AS FailAction
	,CASE WHEN SJ.notify_level_eventlog = 3 THEN ' | LogToWindows' ELSE '' END +
		CASE WHEN SJ.notify_level_email = 3 THEN ' | Email ' + SO_Email.name ELSE '' END +
		CASE WHEN SJ.notify_level_netsend = 3 THEN ' | Netsend ' + SO_Netsend.name ELSE '' END +
		CASE WHEN SJ.notify_level_page = 3 THEN ' | Page ' + SO_Page.name ELSE '' END
	AS CompletedAction
	,SJ.date_modified AS DateModified
	,CONVERT(VARCHAR(25), (SELECT msdb.dbo.agent_datetime(SH_LatestError.run_date, SH_LatestError.run_time)),121) + ': ' + SH_LatestError.[message] AS LatestError
	,tmp.current_step AS CurrentStep
	,CONVERT(CHAR, DATEADD(ms, MIN((SH_Total.run_duration/10000*3600) + ((SH_Total.run_duration/100)%100*60) + (SH_Total.run_duration%100)) * 1000, 0),108) AS MinRunTime
	,CONVERT(CHAR, DATEADD(ms, MAX((SH_Total.run_duration/10000*3600) + ((SH_Total.run_duration/100)%100*60) + (SH_Total.run_duration%100)) * 1000, 0),108) AS MaxRunTime
	,CONVERT(CHAR, DATEADD(ms, AVG((SH_Total.run_duration/10000*3600) + ((SH_Total.run_duration/100)%100*60) + (SH_Total.run_duration%100)) * 1000, 0),108) AS AvgRunTime
FROM
	msdb.dbo.sysjobs AS SJ
LEFT OUTER JOIN
	msdb.dbo.sysjobhistory SH
ON
	SJ.job_id = SH.job_id
AND
	msdb.dbo.agent_datetime(SH.run_date, SH.run_time) > DATEADD(HH, -24, GETDATE())
AND
	SH.step_id = 0
LEFT OUTER JOIN
	msdb.dbo.sysjobhistory SH_Total
ON
	SJ.job_id = SH_Total.job_id
AND
	SH_Total.step_id = 0
LEFT OUTER JOIN
	msdb.dbo.sysjobhistory SH_Latest
ON
	SJ.job_id = SH_Latest.job_id
AND
	SH_Latest.step_id = 0
AND
	SH_Latest.instance_id = (SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory SH_Max WHERE SH_Max.job_id = SH_Latest.job_id)
LEFT OUTER JOIN
	(
		SELECT TOP 1
			T1.job_id
			,T1.run_date
			,T1.run_time
			,T1.[message]
		FROM
			msdb.dbo.sysjobhistory AS T1
		WHERE
			T1.step_id > 0
		AND
			T1.run_status = 0
		ORDER BY
			INSTANCE_ID DESC
	) AS SH_LatestError
ON
	SJ.job_id = SH_LatestError.job_id
LEFT OUTER JOIN
	msdb.dbo.sysjobactivity AS SA
ON
	SJ.job_id = SA.job_id
AND
	SA.next_scheduled_run_date >= GETDATE()
LEFT OUTER JOIN
	msdb.dbo.sysoperators AS SO_Email
ON
	SJ.notify_email_operator_id = SO_Email.id
LEFT OUTER JOIN
	msdb.dbo.sysoperators AS SO_Netsend
ON
	SJ.notify_netsend_operator_id = SO_Email.id
LEFT OUTER JOIN
	msdb.dbo.sysoperators AS SO_Page
ON
	SJ.notify_page_operator_id = SO_Page.id
LEFT OUTER JOIN
	#temp_xp_sqlagent_enum_jobs AS tmp
ON
	SJ.job_id = tmp.job_id
GROUP BY
	CAST(SJ.name AS VARCHAR(256))
	,CAST(SJ.[enabled] AS CHAR(1))
	,ISNULL(SUSER_SNAME(SJ.owner_sid), '')
	,SH_Latest.run_duration
	,msdb.dbo.agent_datetime(SH_Latest.run_date, SH_Latest.run_time)
	,SH_Latest.run_status
	,SJ.notify_level_eventlog
	,SJ.notify_level_email
	,SJ.notify_level_netsend
	,SJ.notify_level_page
	,SO_Email.name
	,SO_Netsend.name
	,SO_Page.name
	,SJ.date_modified
	,msdb.dbo.agent_datetime(SH_LatestError.run_date, SH_LatestError.run_time)
	,SH_LatestError.[message]
	,tmp.current_step
ORDER BY
	Name