sqlserver JOB 監控

1,建立DTS 執行 ,

a .select b.originating_server as HOSTNAME,
       '10.182.15.91' as HOSTIP,
       b.name as JOB_NAME,
       case a.last_run_outcome
         when 0 then
          '作業失敗'
         when 1 then
          '作業成功'
         when 3 then
          '作業取消'
         when 5 then
          '第一次執行'
         else
          'other'
       end LAST_RUN_STATUS,
       /*       'FAIL' as LASR_RUN_STTUS,*/
       a.last_run_time LAST_RUN_TIME,
      
       GETDATE() as RECODE_DATE,
        /*'20120416'  LAST_RUN_DATE*/
       a.last_run_date LAST_RUN_DATE
  from msdb .. sysjobservers a
  left join msdb .. sysjobs b on a.job_id = b.job_id
 where a.last_run_outcome in (0, 1, 3, 5)  and b.enabled = 1

 

b，和oracle監控建立對應的表 連線

create table MSQL_JOB
(
  HOSTNAME        VARCHAR2(30),
  HOSTIP          VARCHAR2(15),
  JOB_NAME        VARCHAR2(200),
  LAST_RUN_STATUS VARCHAR2(10),
  LAST_RUN_TIME   NUMBER,
  RECORD_DATE     DATE,
  LAST_RUN_DATE   NUMBER
)

C,mssql建立JOB定時執行。

2，建立監控product。

   a,自動增加product

   create or replace procedure SendWFL_SQL_ADDJOB is
 num integer;
begin
 --?耞琌?Τ??JOB/DTS
 select nvl(count(*), 0) 
   into num
   from (select distinct (a.job_name) v_name
           from msql_job a
          where to_char(a.record_date, 'YYYYMMDD') =
                to_char(sysdate, 'YYYYMMDD')
            and a.job_name not in (select b.v_name from factory_dts b));
           
 if num >0 then  --?狦??碞秈︽礎?
  for jb in ( select distinct (a.job_name) v_name
   from msql_job a
  where
  to_char(a.record_date, 'YYYYMMDD') = to_char(sysdate, 'YYYYMMDD')
  and a.job_name not in (select b.v_name from factory_dts b)) loop

    insert into factory_dts
    select HOSTIP HOST,
        decode(substr(hostip, 1, 5),
               '10.18',
               '猀盺',
               '10.86',
               '??畄',
               '10.89',
               '瞐?',
               '10.52',
               '犁?') FACTORY,
        'JOB' V_TYPE,
        JOB_NAME V_NAME,
        '' V_DEC,
        '' V_MAN,
        '' status,
        '' code
   from msql_job
   where job_name = jb.v_name and rownum=1;
 
   commit;
 end loop;

 end if;
 
 exception  -- 缽盽矪瞶
  when others then
    null;

end SendWFL_SQL_ADDJOB;
b,失敗監控

 create or replace procedure SendWFL_SQLSERVER is
begin
  for v in (select wfl.seq_wfs_tb_fcsmail.nextval@lk_wfl_wasa1,
                   'DBMS' a2,
                   'DBMS資料庫系統監控@163.com' a3,
                   v_mail a4,
                   '系統來信?' || factory || 'SQLServer_JOB執行失敗:' ||
                   hostip || ',JOB名稱:' || job_name || '' a5,
                   '' || factory || 'SQLServer_JOB執行失敗，主機:' || hostip ||
                   ',JOB名稱' || job_name || '' a6
              from (select a.hostname,
                           b.factory,
                           a.hostip,
                           a.job_name,
                           a.last_run_status,
                           decode(v_mail, '', 'Darcy_Q.L._He/ZDT@ZDT', v_mail) as v_mail,
                           substr(a.last_run_date, 1, 4) || '/' ||
                           substr(a.last_run_date, 5, 2) || '/' ||
                           substr(a.last_run_date, 7, 2) || '  ' ||
                           substr(lpad(a.last_run_time, 6, '0'), 1, 2) || ':' ||
                           substr(lpad(a.last_run_time, 6, '0'), 3, 2) AS last_run_time
                      from msql_job                  a,
                           FACTORY_DTS               b,
                           wfl.wfs_tb_auth@lk_ws c
                     where record_date between
                           (select sysdate - 1 / 4 from dual) and sysdate
                          /*and c.v_type = 'ALL'*/
                       and a.hostip = b.host
                       and decode(trim(b.v_man),
                                  '',
                                  decode(substr(a.hostip, 1, 5),
                                         '10.18',
                                         '廠區一?',
                                         '10.86',
                                         '廠區二,
                                         '10.89',
                                         '廠區三',
                                         '10.52',
                                         廠區三?',
                                         '總部'),
                                  trim(b.v_man)) = c.v_name
                       and c.v_isdel = '0'
                       AND trim(a.job_name) = b.v_name
                       and b.v_type = 'JOB'
                       and last_run_status = '作業失敗) a) loop
 
    insert into wfl.wfs_tb_fcsmail@lk_wfl_40
      (n_id, v_sysid, v_from, v_to, v_subject, v_body)
    values
      (v.a1, v.a2, v.a3, v.a4, v.a5, v.a6);
    commit;
 
  end loop;
 exception  -- 執行失敗

  when others then
    null;
end SendWFL_SQLSERVER;