Get failed jobs report — SQL Server

——–Script to get the failed jobs report

BEGIN
SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY(‘ServerName’)) AS ServerName,
j.name AS job_name,
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’ ELSE
‘Status Unknown’ END AS ‘last_run_status’,jh.message as MessageDetails
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON
ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity)
and jh.run_status = 0 ORDER BY job_name
END