Script to get failed scheduled jobs in SQL Server

As we were talking about to get start time and end time of schedule jobs a week before, We are going to use same information, but it will be with the scheduled jobs having last status is filed for any steps. So I request to go through that post. Also visit post to get running jobs and the way to get it. The DBA needs to know the scheduled job status on routine duty.

Some of the jobs having schedule occurrence one time and some of them have recursive occurrence. Need to list out those jobs having last status is Fail. It may be possible some jobs have multiple steps and among them some fail, so those jobs should be coming into the list. And have to exclude running jobs, even if are failed earlier.

Here I am sharing one script which will alert us with those jobs which failed today and have following criteria,

1. List out failed jobs for today
2. List out jobs which have a last status as fail
3. Need Job Name, Start Time and End Time too
4. Excluding running jobs

USE msdb
GO

-- Creating a temp table
CREATE TABLE #RunningJobs 
( 
    job_id                UNIQUEIDENTIFIER NOT NULL,   
    last_run_date         INT              NOT NULL,   
    last_run_time         INT              NOT NULL,   
    next_run_date         INT              NOT NULL,   
    next_run_time         INT              NOT NULL,   
    next_run_schedule_id  INT              NOT NULL,   
    requested_to_run      INT              NOT NULL,   
    request_source        INT              NOT NULL,   
    request_source_id     sysname          COLLATE database_default NULL,   
    running               INT              NOT NULL,   
    current_step          INT              NOT NULL,   
    current_retry_attempt INT              NOT NULL,   
    job_state             INT              NOT NULL
)   

-- Insert the running jobs in this temp table
INSERT INTO  #RunningJobs  
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa'

SELECT 
 SERVERPROPERTY ('servername') as ServerName,  
 res.name as JobName,
 -- converting run_date and run_time to proper date time format for Start Time
 CAST(
  CONVERT(CHAR(8), run_date, 112) + ' ' + 
  STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
  AS DateTime)     AS StartedAt,  
 -- converting run_date,run_time and run_duration to proper date time format for Finish Time  
 (CONVERT (VARCHAR,
  (DATEADD(ss,
   (CASE LEN(run_duration)
    WHEN 1 
    THEN run_duration
    WHEN 2 
    THEN run_duration
    WHEN 3 
    THEN (CAST(Left(right(run_duration,3),1) as int)*60) 
     + (right(run_duration,2))
    WHEN 4 
    THEN (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + (right(run_duration,2)) 
    WHEN 5 
    THEN (CAST(Left(right(run_duration,5),1) AS int)*3600) 
     + (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + right(run_duration,2) 
    WHEN 6 
    THEN (CAST(Left(right(run_duration,6),2) AS int)*3600) 
     + (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + right(run_duration,2)
     END ),
   CAST(
    CONVERT(CHAR(8), run_date, 112) + ' ' + 
    STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
   AS DateTime)))
  ,120))     AS FailedAt  
 FROM   (SELECT Row_number() 
      OVER( 
        partition BY sj.name 
        ORDER BY run_time DESC) AS rnk, 
       sj.job_id,    
       sj.name, 
       run_date, 
       run_time, 
       run_duration, 
       run_status 
   FROM   msdb.dbo.sysjobhistory sjh WITH (nolock) 
       INNER JOIN msdb.dbo.sysjobs sj WITH (nolock) 
         ON sjh.job_id = sj.job_id 
   WHERE  sjh.step_id <> 0 
       -- considering for today only
       AND run_date = CONVERT(VARCHAR(8), Getdate(), 112) 
       ) res 
 WHERE 
     -- Failed status
     res.run_status = 0
     -- Lastly failed 
     AND res.rnk = 1 
     -- excluding running jobs
     AND NOT EXISTS (SELECT rj.job_id 
         FROM   #runningjobs rj 
         WHERE  rj.job_id = res.job_id 
          AND rj.running = 1) 
-- Droping table
DROP TABLE #RunningJobs

We can also set an alert for those failed jobs in email. I would like you to share your thoughts and the way you are using to get failed job details.