Query para obter histórico dos Jobs no SQL Server Agent

Esta query basicamente retorna o que vemos no "View History" do SQL Server Agent com mais uns pózinhos :)


A query retorna as seguintes colunas:

• JobID: Guid que identifica o Job
• Name: Nome do Job
• StartDate: Data/hora em que o Job começou
• EndDate: Data/hora em que o Job terminou
• Duration: Duração do Job em segundos
• ResultStatus: Resultado da execução do Job
• ResultMessage: Mensagem com os detalhes do resultado da execução do Job

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

SELECT sysjobs.job_id AS JobID ,sysjobs.name AS Name ,CAST( CAST(sysjobhistory.run_date AS VARCHAR) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(sysjobhistory.run_time AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS StartDate ,DATEADD(HOUR, CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 1, 2) AS INT) ,DATEADD(MINUTE, CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 3, 2) AS INT) ,DATEADD(SECOND, CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 5, 2) AS INT) ,CAST( CAST(sysjobhistory.run_date AS VARCHAR) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(sysjobhistory.run_time AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) ) ) ) AS EndDate ,( CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 1, 2) AS INT) * 3600 -- HOURS + CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 3, 2) AS INT) * 60 -- MINUTES + CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 5, 2) AS INT) -- SECONDS ) AS Duration ,CASE WHEN sysjobhistory.run_status = 0 THEN 'Failed' WHEN sysjobhistory.run_status = 1 THEN 'Success' WHEN sysjobhistory.run_status = 4 THEN 'Running' ELSE 'Others (' + CAST(sysjobhistory.run_status AS VARCHAR) + ')' END AS ResultStatus ,sysjobhistory.[message] AS ResultMessage FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobhistory ON (sysjobhistory.job_id = sysjobs.job_id) WHERE sysjobhistory.step_id = 0 -- GET JOB RESULT