-- Audit Query - Failed Jobs Report

DECLARE @PreviousDate varchar(8) 
DECLARE @FinalDate varchar(8) 

-- Initialize Variables 
SET    @PreviousDate = (CONVERT(varchar(8), DATEADD(dd, -1, GETDATE()), 112)); -- Yesterday
SELECT @PreviousDate AS [Start Date]
SET    @FinalDate = (CONVERT(varchar(8), DATEADD(dd, +1, GETDATE()), 112)); -- Tomorrow
SELECT @FinalDate AS [End Date] 

-- Identify failed jobs within last day
SELECT *
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
        INNER JOIN (SELECT DISTINCT job_id FROM msdb..sysjobhistory WHERE run_status <> 1) a 
			ON h.job_id = a.job_id 
WHERE   h.run_status = 0 -- Failure
AND	    h.run_date >= @PreviousDate AND h.run_date < @FinalDate