MS-SQL Server - Job History Script

USE msdb;

GO

SELECT 

    j.name AS [JobName],

    sysdatetime.agent_datetime(jh.run_date, jh.run_time) AS [RunDateTime],

    CASE jh.run_status

        WHEN 0 THEN 'Failed'

        WHEN 1 THEN 'Succeeded'

        WHEN 2 THEN 'Retry'

        WHEN 3 THEN 'Canceled'

        WHEN 4 THEN 'In Progress'

    END AS [RunStatus],

    -- Formats the integer HHMMSS into a structured time window

    STUFF(STUFF(RIGHT('000000' + CAST(jh.run_duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS [Duration_HHMMSS],

    jh.message AS [StatusMessage]

FROM dbo.sysjobs j

INNER JOIN dbo.sysjobhistory jh 

    ON j.job_id = jh.job_id

WHERE jh.step_id = 0 -- 0 reports the overall job status

ORDER BY [RunDateTime] DESC;