Job failed alerts

 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