Audit sql server failed JOBS

----CREATE THIS AS A JOB  and  RUN WEEKLY  and Table resides in MSDB

USE MSDB
GO

--Variable Declarations
DECLARE @PreviousDate DATETIME
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

-- Initialize Variables
SET     @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days can test with 100 days to make sure this is working
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
----This table resides in MSDB   , DO FIREST as INSERT INTO
INSERT INTO AGENT_History
SELECT   j.[name] AS Job_name,
         s.Step_Name,
         h.Step_Id,
      CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS DATETIME), 111) RunDate,
      STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime, 
         h.Sql_Severity,
         --h.run_duration StepDuration,
CASE h.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus, 
h.run_duration StepDuration,
         h.Message,
         h.Server

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 -- Failure
         AND h.run_date > @FinalDate
--WHERE h.run_date > @FinalDate all days jobs
ORDER BY h.instance_id DESC


GO



Check daily or when ever needed in table
SELECT * FROM AGENT_History