Verifica os últimos status dos Jobs - SQL Server

USE msdb
GO

/*
Consulta que informa o status da ultima execução de cada job
Esta deve ser executada diáriamente
*/

SELECT
Name, 
CONVERT(DATETIME, MAX(lastrun)) AS [LastRun],
CASE len([run duration])
WHEN 1 THEN CAST('00:00:0' + CAST([run duration] AS CHAR) AS CHAR (8))
WHEN 2 THEN CAST('00:00:' + CAST([run duration] AS CHAR) AS CHAR (8))
WHEN 3 THEN CAST('00:0' + LEFT(RIGHT([run duration], 3), 1) + ':' + RIGHT([run duration], 2) AS CHAR (8))
WHEN 4 THEN CAST('00:' + LEFT(RIGHT([run duration], 4), 2) + ':' + RIGHT([run duration], 2) AS CHAR (8))
WHEN 5 THEN CAST('0' + LEFT(RIGHT([run duration], 5), 1) + ':' + LEFT(RIGHT([run duration], 4), 2) + ':' + RIGHT([run duration], 2) AS CHAR (8))
WHEN 6 THEN CAST(LEFT(RIGHT([run duration], 6), 2) + ':' + LEFT(RIGHT([run duration], 4), 2) + ':' + RIGHT([run duration], 2) AS CHAR (8))
END AS [RunDuration],
Status 
FROM
(SELECT 
j.[name],
CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':') AS [LastRun],
CASE jh.run_status 
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status
,jh.run_date      AS [last run date]
,jh.run_time      AS [last run time]
,js.next_run_date AS [next run date]
,js.next_run_time AS [next run time]
,jh.run_duration  AS [run duration]
FROM 
msdb.dbo.sysjobs j LEFT OUTER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
WHERE
j.enabled = 1 AND CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':') = 
(SELECT 
MAX(CONVERT(VARCHAR, t.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,t.run_time),6),5,0,':'),3,0,':')) 
FROM 
msdb.dbo.sysjobhistory t WHERE t.job_id = jh.job_id)
GROUP BY 
j.name, 
js.next_run_date, 
js.next_run_time, 
jh.run_date, 
jh.run_time, 
jh.run_status,
jh.run_duration) t
GROUP BY 
t.name, t.status, t.[run duration]
ORDER BY 
t.name