Returns any jobs that have failed within the last 7 days and at what date and time they were supposed to have run.

FailedJobs.sql

SELECT
	[Job].[name] [JobName],
	[Category].[name] [CategoryName],
	[History].[step_name] [StepName],
	CAST(CAST([History].[run_date] AS varchar(8)) AS date) [RunDate],
	CAST(CAST([History].[run_time] / 10000 AS varchar(2)) + ':' + CAST(([History].[run_time] % 10000) / 100 AS varchar(2)) + ':' + CAST(([History].[run_time] % 100) AS varchar(2)) AS time(0)) [RunTime],
	[History].[message] [Message]
FROM msdb.dbo.sysjobs [Job]
INNER JOIN msdb.dbo.syscategories [Category] ON [Job].[category_id] = [Category].[category_id]
CROSS APPLY
(
	SELECT *
	FROM msdb.dbo.sysjobhistory [JobHistory]
	WHERE [run_status] = 0
	AND CAST(CAST([run_date] AS varchar(8)) AS date) >= DATEADD(d, -7, GETDATE())
	AND [JobHistory].[job_id] = [Job].[job_id]
	AND [step_id] <> 0
) [History]