Query to Retrieve Job Info from msdb Database


SELECT
-- Format date
((SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 1,4) + '-' +
SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 5,2) + '-' +
SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 7,2)) + ' ' +

-- Format time
(
-- Hours
CASE
WHEN CONVERT(VARCHAR(20), H.run_time/10000) > 12 THEN CONVERT(VARCHAR(20), (H.run_time/10000) -12)
WHEN CONVERT(VARCHAR(20), H.run_time/10000) = 0 THEN '12'
ELSE CONVERT(VARCHAR(20), H.run_time/10000)
END + ':' +

-- Minutes
CASE LEN(CONVERT(VARCHAR(20), H.run_time/100%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_time/100%100)
ELSE CONVERT(VARCHAR(20), H.run_time/100%100)
END + ':' +

--Seconds
CASE LEN(CONVERT(VARCHAR(20), H.run_time%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_time%100)
ELSE CONVERT(VARCHAR(20), H.run_time%100)
END

+ ' ' +

-- AM or PM
CONVERT(VARCHAR(3),
CASE
WHEN CONVERT(VARCHAR(20), H.run_time/10000) > 12
THEN ' PM'
ELSE ' AM'
END

))) AS [Last Run Time],

S.NAME AS [Job Name], H.Message,

-- Format Status
CASE H.run_status
WHEN 1 THEN 'Success'
ELSE 'Failure'
END AS Status,

-- Format Run_Duration
(CASE LEN(CONVERT(VARCHAR(20), H.run_duration/10000))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration/10000)
ELSE CONVERT(VARCHAR(20), H.run_duration/10000)
END + ':' +

CASE LEN(CONVERT(VARCHAR(20), H.run_duration/100%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration/100%100)
ELSE CONVERT(VARCHAR(20), H.run_duration/100%100)
END + ':' +

CASE LEN(CONVERT(VARCHAR(20), H.run_duration%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration%100)
ELSE CONVERT(VARCHAR(20), H.run_duration%100)
END) AS [Run Duration]

FROM [msdb].[dbo].[sysjobhistory] AS H
INNER JOIN [msdb].[dbo].[sysjobs] AS S
ON S.job_id = H.job_id
WHERE S.enabled = 1
AND H.step_id = 0
ORDER BY H.RUN_DATE desc, H.RUN_TIME DESC