Audit Vault Collector Status Check & Send Automatic E-mail

Audit Vault 10.3 sürümünü kullanırken collector’lerin farklı zamanlarda farklı sebeplerden dolayı kapandığına şahit oldum, tabi bu collector’lerin sürekli up durumunda olmaları gerekir ki audit kayıtlarını loglayabilsinler, bu yüzden zamanında aşağıdaki gibi bir otomatik alert mekanizması kurmuştum;

CREATE TABLE av_collector_status
(
   log_date       DATE DEFAULT SYSDATE,
   collector_id   NUMBER,
   collector      VARCHAR2 (50),
   agent          VARCHAR2 (20),
   source         VARCHAR2 (20),
   bps            VARCHAR2 (20),
   rps            VARCHAR2 (20),
   ia             VARCHAR2 (10)
);

CREATE TABLE av_collector_status_history
(
   log_date       DATE DEFAULT SYSDATE,
   collector_id   NUMBER,
   collector      VARCHAR2 (50),
   agent          VARCHAR2 (20),
   source         VARCHAR2 (20),
   bps            VARCHAR2 (20),
   rps            VARCHAR2 (20),
   ia             VARCHAR2 (10)
);

CREATE OR REPLACE PROCEDURE AV_COLLECTOR_STATUS_CHECK
IS
   -- Orhan Eripek 16.12.2013

   v_dyntask   VARCHAR2 (2000);
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE av_collector_status';

   COMMIT;
   v_dyntask :=
      'insert all
      into av_collector_status_history ( collector_id, collector,agent, source, bps,rps,ia)
      into av_collector_status ( collector_id, collector,agent, source, bps,rps,ia)
select collector_id, collector,agent, source, bps,rps,ia from
(SELECT collector.collector_id,
         collector.collector_name collector,
         agent.agent_name agent,
         source.source_name source,
         bps_metric.metric_value bps,
         rps_metric.metric_value rps,
         ia_metric.metric_value ia
    FROM avsys.av$source source,
         avsys.av$collector collector,
         avsys.av$agent agent,
         (SELECT id, metric_value
            FROM AVSYS.av$valid_metrics metrics
           WHERE     metric_name = ''BYTES_PER_SEC''
                 AND metrics.CATEGORY = ''COLLECTOR'') bps_metric,
         (SELECT id, metric_value
            FROM AVSYS.av$valid_metrics metrics
           WHERE metric_name = ''IS_ALIVE'' AND metrics.CATEGORY = ''COLLECTOR'') ia_metric,
         (SELECT id, metric_value
            FROM AVSYS.av$valid_metrics metrics
           WHERE     metric_name = ''RECORDS_PER_SEC''
                 AND metrics.CATEGORY = ''COLLECTOR'') rps_metric
   WHERE     collector.source_id = source.source_id
         AND collector.agent_id = agent.agent_id
         AND source.status != 0
         AND collector.status != 0
         AND collector.collector_id = bps_metric.id
         AND collector.collector_id = ia_metric.id
         AND collector.collector_id = rps_metric.id
ORDER BY collector.collector_name)';

   EXECUTE IMMEDIATE v_dyntask;

   COMMIT;
END; 
/

CREATE OR REPLACE PROCEDURE AV_COLLECTOR_STATUS_EMAIL
IS

-- Orhan Eripek 16.12.2013

v_text long;
v_col varchar2(200);

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''10.10.10.10''';
    
for text in (select to_char(log_date,'dd.mm.yyyy hh24:mi') as log_date, collector_id,
 collector,agent, source, bps,rps,ia from avsys.av_collector_status
where collector_id in ('21','22','23'))
loop
       
    if text.ia='false' then  
    
    v_text:=v_text||'"Log_date"         '||'"Collector" '||'       "Agent"  '||' 
        "Audit_Source" '||'     "Bytes_Per_Second"  '||'     "Records_Per_Second"'||'
   "Status" '||chr(10);
    v_text:=v_text||'________'||'   ________'||'     ________'||'      ________'||'
     _____________  '||'         ______________'||'       ______'||chr(10);                                                                                                                                                                                                                                                                                                                                                       ||chr(10);
 
  v_text:=v_text||text.log_date||'   '||text.collector||'    '||text.agent||' 
  '||text.source||'   '||text.bps||'   '||text.rps||'   '||text.ia||'   ' ||chr(10);

    v_col:= text.collector;

  UTL_MAIL.send(sender => '...@...com.tr',
           recipients =>  '....@...com.tr', 
           subject => 'Otomatik Alert Mail: Prod Audit Vault''un  "'||v_col||'" Collector''ü kapanmıştır!!!',
           message => v_text,
           mime_type => 'text; charset=us-ascii'); 
             
v_text:='';                      
END IF;