USE [msdb]
;WITH CTE_MostRecentJobRun AS
(
SELECT job_id,run_status,run_date,run_time
,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk
FROM sysjobhistory
WHERE step_id=0
)
SELECT
name AS [Job Name]
,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60
+((run_time - (run_time/10000) * 10000)/100) * 60
+ (run_time - (run_time/100) * 100) ,
CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
,CASE WHEN enabled=1 THEN 'Enabled'
ELSE 'Disabled'
END [Job Status],
CASE WHEN run_status=0 THEN 'FAILED'
ELSE 'SUCCESS'
END [Status]
FROM CTE_MostRecentJobRun MRJR
JOIN sysjobs SJ
ON MRJR.job_id=sj.job_id
WHERE Rnk=1
/*AND run_status=0 -- i.e. failed */
ORDER BY name