监控失败作业

;WITH j_max
AS
(
    SELECT [RowNum], [JobName], [StepID], [StepName], [StepCommand], [RunStatus], [Message], [RunBeginTime], [RunEndTime]
    FROM
       (
        SELECT ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY h.instance_id DESC) AS [RowNum],
               j.name AS [JobName],
               s.step_id AS [StepID],
               s.step_name AS [StepName],
               s.command AS [StepCommand],

               (case  when h.run_status=0 then 'Failed'  
                when h.run_status= 1 then 'Succeeded'
                when h.run_status= 2 then 'Retry'
                when h.run_status= 3 then 'Canceled'
                else 'Unknown'  
                end 
                ) AS [RunStatus], 
          
                h.[Message] AS [Message], 

                msdb.dbo.agent_datetime(run_date, run_time) AS [RunBeginTime] ,
         
                h.run_duration / 10000 * 3600 + (h.run_duration % 10000) / 100 * 60  + h.run_duration % 100 AS [RunDurationScd],
         
                DATEADD( s, 
                         (h.run_duration / 10000 * 3600 + (h.run_duration % 10000) / 100 * 60  + h.run_duration % 100), 
                         msdb.dbo.agent_datetime(run_date, run_time)
                        ) AS [RunEndTime] 
 
          FROM   msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobsteps s   ON j.job_id = s.job_id AND j.enabled = 1   --Only Enabled Jobs
         INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id AND s.step_id = h.step_id
                                                  --AND h.step_id <> 0 --一个作业所有步骤的累计时间(注s表中step_id均不为0)
        ) j_max
    WHERE j_max.[RowNum]=1 
)



SELECT [RunBeginTime],

       (N'[192.168.88.125]服务器有Job失败：
        [JobName]：' +[JobName]+ N',
        [StepName]:' +[StepName]+ N'
        [RunBeginTime]:' +CONVERT(CHAR(16), [RunBeginTime], 121) + N'
        [Message]:' +[Message]
        ) AS msgcontent,
       
       '15316967290' AS desttermid
  INTO #emap_sm_mt_send
  FROM j_max 
 WHERE [RunStatus]='Failed'




 IF EXISTS(SELECT 1 FROM #emap_sm_mt_send WHERE (   DATEDIFF( HH, [RunBeginTime], GETDATE() )<=1 
                                                 OR DATEDIFF( HH, [RunBeginTime], GETDATE() )>=24
                                                 )
          )
 INSERT INTO [192.168.1.100].emap_mdao.dbo.emap_sm_mt_send(msgcontent ,desttermid) 
 SELECT msgcontent, desttermid
   FROM #emap_sm_mt_send