做了一个作业简单的监控工具,实现对几个服务器的作业进行监控,当作业执行错误时,即时发送邮件到几个指定的邮箱,主要是用timer循环查获作业的历史日志记录。
SELECT [JobName] = JOB.name, [Step] = HIST.step_id, [StepName] = HIST.step_name, [Message] = HIST.message, [Status] = CASE WHEN HIST.run_status = 0 THEN 'Failed' WHEN HIST.run_status = 1 THEN 'Succeeded' WHEN HIST.run_status = 2 THEN 'Retry' WHEN HIST.run_status = 3 THEN 'Canceled' END, [RunDate] = HIST.run_date, [RunTime] = HIST.run_time, [Duration] = HIST.run_duration FROM sysjobs JOB INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id WHERE HIST.run_date>=CONVERT(CHAR(8),GETDATE()-1,112) AND HIST.run_status<>1
and JOB.name = 'jobnamemodel' and HIST.run_date='rundatemodel' and HIST.run_time between 'runtimemodel1' and 'runtimemodel2' ORDER BY HIST.run_date, HIST.run_time