Job_FailaureMonitoring.sql

CREATE PROC [dbo].[Job_FailaureMonitoring]
	@HourInterval int = 24
   ,@recipients varchar(4000)
   ,@profile_name nvarchar(128)
   ,@debug bit = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
	declare @body varchar(max)
		  , @ServerName varchar(100)
		  , @table_title varchar(200)
		  , @db_email_profile varchar(40)
	Select @ServerName = @@SERVERNAME
		 , @table_title = '['+@ServerName+']'+'SQL Server Job - Failed Job Report'

	IF not exists (select 1
		  from [msdb].[dbo].[sysmail_profile] p
		 where name = @profile_name) begin		
		SELECT @db_email_profile=p.name
         FROM [msdb].[dbo].[sysmail_principalprofile] pp
            JOIN [msdb].[dbo].[sysmail_profile] p
               ON pp.profile_id = p.profile_id
       WHERE pp.[is_default] = 1
	end else begin
		SET @db_email_profile=@profile_name
	end

	set @body = cast( (
	select td = 'class="' + tr_class + '"><td>' + servername + '</td><td>' + job_name + 
				'</td><td>' + convert(varchar(20),start_execution_date,112) +
				'</td><td>' + convert(varchar(20),stop_execution_date,112) + '</td><td>' + convert(varchar(10),run_duration) +
				'</td><td>' + run_status + '</td><td>' + convert(varchar(5),last_step_id) +
				'</td><td>' + convert(varchar(max),message) + '</td><td>' + convert(varchar(max),job_description)
	  from (
				select d.servername
							 , d.job_name, d.start_execution_date, d.stop_execution_date, d.run_duration
							 , d.run_status
							 , d.last_step_id, isnull(m.SSISMessage,d.message) message
							 , d.job_description
							 , d.tr_class, d.PackageName
				  from (
							select @@ServerName servername
									 , j.name job_name, start_execution_date, stop_execution_date, run_duration
									 , case run_status when 0 then 'Failed' when 1 then 'Succeeded'
									   when 2 then 'Retry' when 3 then 'Canceled' else 'N/A' end run_status
									 , last_step_id, message
									 , description job_description
									 , case when row_number() over(order by j.name)%2 = 1 then 'odd' else 'even' end  tr_class
									 , case when s.command LIKE '%.dtsx%' then RIGHT(LEFT(s.command, Charindex('.dtsx', s.command)-1),
									   Charindex('\', Reverse(LEFT(s.command, Charindex('.dtsx', s.command)-1)))-1) + '.dtsx' end PackageName
									, s.subsystem
								  from msdb.dbo.sysjobs j
								 cross apply (
									   select top 1 
											  start_execution_date, last_executed_step_id, last_executed_step_date
											, stop_execution_date, job_history_id
										 from msdb.dbo.sysjobactivity ja
										where ja.job_id = j.job_id
										  and ja.start_execution_date >= dateadd(hh,24*-1,getdate())
										  and ja.start_execution_date <= getdate()
										order by run_requested_date desc
										) ja
								 cross apply (
									   select top 1
											  jh.step_id, jh.run_status, jh.run_duration
											, jhl.step_id last_step_id, jhl.run_status last_step_run_status
											, jhl.message
										 from msdb.dbo.sysjobhistory jh
										 join msdb.dbo.sysjobhistory jhl
										   on jh.job_id = jhl.job_id
										  and jh.instance_id >= jhl.instance_id
										  and jhl.step_id <> 0
										where jh.job_id = j.job_id 
										  and jh.instance_id = ja.job_history_id
										  and jh.step_id = 0
										  and jh.run_status = 0 -- 0: Failed, 3: Canceled
										order by jhl.instance_id desc
									   ) jh
							 join msdb.dbo.Sysjobsteps s (NOLOCK)
								 on s.Job_id = j.job_id
							   aND s.Step_id = jh.last_step_id
							  ) as d
					  outer apply (
									select '<table cellpadding="2" cellspacing="2" border="1">' +
												'<tr><th>message time</th><th>Messages</th><th>message_source_name</th><th>subcomponent_namee</th></tr>' +
												replace(replace( replace( cast( (
												select td = convert(varchar(20),em.message_time,112) + '</td><td>' + isnull(em.message,'') + 
															'</td><td>' +isnull(em.message_source_name,'') +
															'</td><td>' + isnull(em.subcomponent_name,'')
												 from (
														select em.[event_message_id], em.message_source_name, em.subcomponent_name, em.message_time, em.message
														  from (select top 1 e.execution_id operation_id
																	  from ssisdb.ssisdb.[internal].[executions] e with(nolock)
																	where e.package_name=d.PackageName
																	and d.subsystem='SSIS'
																	order by e.execution_id desc
																	) o
															cross apply(
																	select em.[event_message_id], em.message_source_name, em.subcomponent_name, om.message_time, om.message
																	   from ssisdb.ssisdb.internal.event_messages em with(nolock)
																	   join ssisdb.ssisdb.[internal].[operation_messages] om with(nolock)
																		  on om.[operation_message_id] = em.[event_message_id]
																	 where o.operation_id=em.operation_id
																	 --and em.package_name=d.PackageName
																	 and em.Event_name = 'OnError'
																) em
														) em
											order by em.message_time, em.[event_message_id]
											for xml path( 'tr' ) ) as varchar(max) )
												, '&lt;', '<' ), '&gt;', '>' ), '><td>class="', ' class="') +
												'</table>' SSISMessage
							) m
		  ) as d
	for xml path( 'tr' ) ) as varchar(max) )
	
	IF @body IS NULL
		RETURN 0

	set @body = '<style> ' +
				'TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;} ' +
				'TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;} ' +
				'TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;} ' +
				'.odd  { background-color:#ffffff; } ' +
				'.even { background-color:#dddddd; } ' +
				'</style>'
			  + '<table cellpadding="2" cellspacing="2" border="1">'
			  + '<tr><th>Server Name</th><th>Job Name</th><th>Start Exec Date</th><th>Stop Exec Date</th><th>Run Duration</th><th>Run Status</th><th>Last Step</th><th>Error Message</th><th>Job Description</th></tr>'
			  + replace(replace( replace( @body, '&lt;', '<' ), '&gt;', '>' ), '><td>class="', ' class="')
			  + '</table>'

	IF @debug = 1
		print @body

	EXEC msdb.dbo.sp_send_dbmail @profile_name = @db_email_profile,
	@recipients = @recipients,
	--@copy_recipients = '',
	@body = @body,
	@body_format = 'HTML',
	@importance = 'Normal', -- high,normal,low
	@subject = @table_title
RETURN 0
END TRY
BEGIN CATCH
    -- Error information (if you want it returned as a result set)
    SELECT  ERROR_LINE() AS [Error_Line],
            ERROR_MESSAGE() AS [Error_Message],
            ERROR_NUMBER() AS [Error_Number],
            ERROR_SEVERITY() AS [Error_Severity],
            ERROR_PROCEDURE() AS [Error_Procedure];
	THROW;
	RETURN -1;
END CATCH
go