How to setup the job failure notification alert JOB when any job fails in "n" number of SQL Instances

There are several ways to get the failure notification alert when the SQL Agent job fails. One of the easiest ways is to configure the notification when setting up each and every individual job(s) like below.

But if support engineer misses to set up the notification when deploying the new patch which modifies/includes existing/additional job in the production environment then notification alert would not come in case if this job fails. 

It is always better to have one central job, which checks the last job status of all jobs in each SQL Instance and sends an alert about the failed job information. 

In order to connect to each SQL Instance and check the job status, one can either develop an SSIS package with for each loop get failure job(s) information and send information to the email or 

the easiest approach is to create a linked server for each SQL Instance and execute below T-SQL statement in SQL job.

USE master 
GO 
SET NOCOUNT ON 
DROP TABLE IF EXISTS #JOB_FAILURES 
DROP TABLE IF EXISTS #SQL_INSTANCES 
CREATE TABLE #JOB_FAILURES (SQL_INSTANCE_NAME SYSNAME,JOB_NAME NVARCHAR(128),LAST_RUN_DATE NVARCHAR(128),LAST_OUTCOME_MESSAGE VARCHAR(MAX))
SELECT * INTO #SQL_INSTANCES 
FROM (VALUES ('localhost'), ('NARA'), ('SIMHA'), ('NARASIMHA')) AS t(SQL_INSTANCE) 
--SELECT * FROM #SQL_INSTANCES 
WHILE EXISTS (SELECT * FROM #SQL_INSTANCES) 
BEGIN 
   
  DECLARE @SQLCMD NVARCHAR(MAX) = '' 
  DECLARE @SQL_INSTANCE SYSNAME ='' 
  SELECT TOP 1 @SQL_INSTANCE = SQL_INSTANCE FROM #SQL_INSTANCES 
  SET @SQLCMD = 'IF (OBJECT_ID(''tempdb..##JOB_FAILURE'') IS NOT NULL) DROP TABLE ##JOB_FAILURE SELECT  [name] JOB_NAME,LAST_RUN_DATE, LAST_OUTCOME_MESSAGE INTO ##JOB_FAILURE FROM ['+@SQL_INSTANCE+'].MSDB.dbo.sysjobservers sjs JOIN ['+@SQL_INSTANCE+'].MSDB.dbo.sysjobs sj on sj.job_id=sjs.job_id WHERE last_run_outcome = 0'
  PRINT @SQLCMD 
  EXEC SP_EXECUTESQL @SQLCMD 

  INSERT #JOB_FAILURES (SQL_INSTANCE_NAME,JOB_NAME,LAST_RUN_DATE,LAST_OUTCOME_MESSAGE) 
  SELECT @SQL_INSTANCE,JOB_NAME,LAST_RUN_DATE,LAST_OUTCOME_MESSAGE FROM ##JOB_FAILURE 
  DELETE #SQL_INSTANCES WHERE SQL_INSTANCE = @SQL_INSTANCE 
END 
IF EXISTS (SELECT * FROM #JOB_FAILURES) 
BEGIN 
DECLARE @xml NVARCHAR(MAX) 
DECLARE @body NVARCHAR(MAX) 
DECLARE @bodyHtml NVARCHAR(MAX) 
DECLARE @mailSubject NVARCHAR(MAX) 
DECLARE @STMT VARCHAR(100) 
DECLARE @RtnCode INT 
DECLARE @recipients NVARCHAR(MAX) 
     
DECLARE @ResultHTML  NVARCHAR(MAX) ;SET @ResultHTML = 
  CAST ( ( SELECT 
          '<td style="border: 1px solid black;">' + SQL_INSTANCE_NAME + '</td>' + 
          '<td style="border: 1px solid black;">' + JOB_NAME + '</td>' + 
          '<td style="border: 1px solid black;">' + LAST_RUN_DATE + '</td>' + 
          '<td style="border: 1px solid black;">' + LAST_OUTCOME_MESSAGE + '</td>' --+ 
        FROM ( 
          SELECT DISTINCT SQL_INSTANCE_NAME,JOB_NAME,cast(LAST_RUN_DATE as nvarchar) as LAST_RUN_DATE,LAST_OUTCOME_MESSAGE FROM #JOB_FAILURES
          ) AS TT 
          ORDER BY SQL_INSTANCE_NAME,JOB_NAME,LAST_RUN_DATE 
        FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) 
SET @ResultHTML = 
  N'<div style="height:20px">' + 
  N'<h3><font face="verdana" color="Black" font size="2">Below are the FAILED Job(s) Information:'+ CONVERT(VARCHAR,GETDATE(),103)  +'</font></h3>'+ 
  N'</div>' + 
  N'<style type=''text/css''> '+ 
  N'td{font-family:verdana; color:black; font-size:10pt;}' + 
  N'</style>' + 
  N'<table style="border: 2px solid black; width: 80%; border-collapse: collapse;">' + 
  N'<tr>' + 
  N'<th style="border: 1px solid black; width: 15%; background-color: #A9A9A9">SQL_INSTANCE_NAME</th>' + 
  N'<th style="border: 1px solid black; background-color: #A9A9A9">JOB NAME</th>' + 
  N'<th style="border: 1px solid black; background-color: #A9A9A9">LAST_RUN_DATE</th>' + 
  N'<th style="border: 1px solid black; background-color: #A9A9A9">LAST_OUTCOME_MESSAGE</th>' + 
  N'</tr>' + 
  REPLACE( REPLACE( @ResultHTML, '&lt;', '<' ), '&gt;', '>' ) + 
  N'</table>' + 
  --N' 
  N'<br/><br/>' + 
  N'<font face="verdana" color="Black" font size="2"><b>Note:</b> This is an auto generated mail. Please do not reply to this mail.</font>' ;

SET @mailSubject = 'JOB FAILURE NOTIFICATION ALERT' 
SET @body = @ResultHTML 

EXEC  @RtnCode = msdb..sp_send_dbmail 
    @profile_name = 'Narasimha_Profile', 
    @body = @body, 
    @body_format ='HTML', 
    @recipients='Mandalapu@live.com', 
    @copy_recipients = 'Narasimha@live.com', 
    @blind_copy_recipients = 'Narasimha@live.com', 
    @subject = @mailSubject 
END

I have created a linked server to the same server with a different name and executed it. Below is the screenshot.

I have received the email after executing it, please see the screenshot.

Kindly let me know in case if you have any easier approach. 

Thank you,

Narasimha