View to obtain Failed Jobs from SQL Server Agent

CREATE VIEW dbo.V_Failed_Jobs
AS
SELECTJJ.instance_id
,sj.job_id
,sj.name as 'JOB_NAME'
,sjt.step_name as 'STEP_NAME'
,JJ.run_status
,JJ.sql_message_id
,JJ.sql_severity
,JJ.message
,JJ.exec_date
,JJ.run_duration
,JJ.server
,sjt.output_file_name
FROM(SELECTssh.instance_id
,sjh.job_id
,sjh.step_id
,sjh.sql_message_id
,sjh.sql_severity
,sjh.message
,(CASE sjh.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 run_status
,((SUBSTRING(CAST(sjh.run_date AS VARCHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST(sjh.run_date AS VARCHAR(8)), 7, 2) + '/' 
+ SUBSTRING(CAST(sjh.run_date AS VARCHAR(8)), 1, 4) + ' '
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(sjh.run_time AS varchar))) 
+ CAST(sjh.run_time AS VARCHAR)), 1, 2) + ':' 
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(sjh.run_time AS VARCHAR))) 
+ CAST(sjh.run_time AS VARCHAR)), 3, 2) + ':'
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(sjh.run_time as varchar)))
+ CAST(sjh.run_time AS VARCHAR)), 5, 2))) AS 'exec_date'
,sjh.run_duration
,sjh.retries_attempted
,sjh.server
FROMmsdb.dbo.sysjobhistory sjh
JOIN(SELECTsjh.job_id
,sjh.step_id
,MAX(sjh.instance_id) as instance_id
FROMmsdb.dbo.sysjobhistory sjh
GROUP BYsjh.job_id
,sjh.step_id
) AS ssh ON sjh.instance_id = ssh.instance_id
WHERE sjh.run_status <> 1
) AS JJ
JOINmsdb.dbo.sysjobs sj 
ON (jj.job_id = sj.job_id)
JOINmsdb.dbo.sysjobsteps sjt
ON (jj.job_id = sjt.job_id AND jj.step_id = sjt.step_id)
GO