Today, I’m sharing the following T-SQL script which you can use to find which SQL Server Agent Jobs failed yesterday. I use this script as part of my daily server monitoring SSIS package, which executes this script on all production SQL server and then sends email to our group:

SET NOCOUNT ON;
DECLARE  @Value           [varchar](2048)
        ,@JobName         [varchar](2048)
        ,@PreviousDate    [datetime]
        ,@Year            [varchar](4)
        ,@Month           [varchar](2)
        ,@MonthPre        [varchar](2)
        ,@Day             [varchar](2)
        ,@DayPre          [varchar](2)
        ,@FinalDate       [int]
-- Declaring Table variable
DECLARE @FailedJobs TABLE ([JobName] [varchar](200))
-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) 
SET @Year = DATEPART(yyyy, @PreviousDate) 
SELECT @MonthPre = CONVERT([varchar](2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT([varchar], (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT([varchar](2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT([varchar], (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS [int])
-- Final Logic
INSERT INTO @FailedJobs
SELECT DISTINCT j.[name]
FROM [msdb].[dbo].[sysjobhistory] h
INNER JOIN [msdb].[dbo].[sysjobs] j
 ON h.[job_id] = j.[job_id]
INNER JOIN [msdb].[dbo].[sysjobsteps] s
 ON j.[job_id] = s.[job_id]
  AND h.[step_id] = s.[step_id]
WHERE h.[run_status] = 0
 AND h.[run_date] > @FinalDate
SELECT @JobName = COALESCE(@JobName + ', ', '') + '['+ [JobName] + ']'
FROM @FailedJobs
SELECT @Value = 'Failed SQL Agent job(s) found: '+ @JobName + '. '
IF @Value IS NULL 
BEGIN  
 SET @Value = 'None.'
END   
SELECT @Value

I hope you will find this script useful