Use polaris

DROP TABLE IF EXISTS #FullDelta


DECLARE @FinalDate INT;
SET @FinalDate = CONVERT(int
    , CONVERT(varchar(10), DATEADD(DAY, -1, GETDATE()), 112)
    ) -- Yesterday's date as Integer in YYYYMMDD format


IF (SELECT COUNT(*)
FROM msdb.dbo.sysjobhistory h  
INNER JOIN msdb.dbo.sysjobs j  
    ON h.job_id = j.job_id  
WHERE    h.run_status = 0 -- Failure  
AND h.step_id != 0
AND ((h.run_date = @FinalDate AND h.run_time > 60000)
    OR h.run_date > @FinalDate)
AND j.name NOT IN (
	'Cataloging Background Tasks'
	)
	) >= 1

BEGIN

CREATE TABLE #FullDelta 
(
	Job_Name varchar(max), 
	Step_ID int, 
	Step_Name Varchar(max), 
	Run_Date datetime, 
	SQL_Severity int, 
	[Message] VARCHAR(max)
)

INSERT INTO #FullDelta (Job_Name, Step_ID, Step_Name, Run_Date, SQL_Severity, [Message])

SELECT
    j.[name],  
    h.step_id,   
    s.step_name,  
    msdb.dbo.agent_datetime(h.run_date,h.run_time) run_date,  
    h.sql_severity,  
    h.message
FROM msdb.dbo.sysjobhistory h  
INNER JOIN msdb.dbo.sysjobs j  
    ON h.job_id = j.job_id  
LEFT 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.step_id != 0
AND ((h.run_date = @FinalDate AND h.run_time > 60000)
    OR h.run_date > @FinalDate)
AND j.name NOT IN (
	'Cataloging Background Tasks'
	)
ORDER BY 
    msdb.dbo.agent_datetime(h.run_date,h.run_time) asc,
    j.[name]

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)


SET @xml = CAST(( SELECT 
	'top' AS "td/@valign",
	[Job_Name]AS 'td','',
	'top' AS "td/@valign",
	[Step_ID] AS 'td','',
	'top' AS "td/@valign",
	[Step_Name] AS 'td','',
	'top' AS "td/@valign",
	[Run_Date] AS 'td','',
	'top' AS "td/@valign",
	[SQL_Severity] AS 'td','',
	'top' AS "td/@valign",
	[Message] AS 'td'
FROM  #FullDelta ORDER BY Run_Date ASC, Job_Name
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))


SET @body ='<html><body text="black"><H3>Failed Jobs & Job Steps from Yesterday</H3>
<p>This alert is generated if any jobs and/or job steps failed yesterday.  Note that the SQL_Severity does not actually align to code from Microsoft, it''s a number that aligns to the message.</p>
<BR>

<table cellpadding="5" cellspacing="0" border="1">
<tr>
	<th width = 130px align= left> Job_Name </th> 
	<th width = 55px align= left> Step_ID </th> 
	<th width = 130px align= left> Step_Name </th> 
	<th width = 80px align= left> Run_Date </th> 
	<th width =90px align= left> SQL_Severity </th> 
	<th width =350px align= left> Message </th></tr>'

 
SET @body = @body + @xml +'</table></body></html>'


exec msdb.dbo.sp_send_dbmail
@profile_name = '', -- the email profile set-up on SQL Server
@recipients = '', -- recipient emails, separated by a semi-colon
@subject = 'Failed Jobs / Job Steps',
@body = @body,
@body_format = 'HTML';


DROP TABLE #FullDelta

END
