SN67 Monitoring Applications without Application Development

Document created by brian.wolff@hpe.com on Sep 2, 2009Last modified by RKingsland on Jul 8, 2014
Version 5Show Document
  • View in full screen mode

SN67

Monitoring Applications without Application

Development

Speakers:

 

Demand for the logging of applications has grown;

however, many applications today do not log

transactions. This session will discuss how to enable

application logging through the database, without

changing the application code. Examples using Oracle

 

 

I have already received requests walking the halls here at Protect09.

The request has been for the code sample that shows how to unload an Oracle table directly into CEF format as described during this presentation

 

Over the next day or so I will be posting the complete mini-lesson as it is developed.

 

---

This code example shows how to unload an oracle table into CEF format for ArcSight

 

First you need to create the table to hold the audited values.

-- Run as sys
connect sys/arcsight as sysdba; -- Change to your credentials
grant dba to order_audit identified by arcsight;
grant resource to order_audit;
grant all on utl_file to order_audit;

connect order_audit/arcsight


create table order_audit
(END_TIME  TIMESTAMP(3),

  Activity_Code varchar2(3),

  Cust_Phone  varchar2(12),
  Cust_Addr  varchar2(30),
  Order_num  varchar2(5),
  order_src  varchar2(10));
 
  delete from order_audit;
 
  insert into order_audit values (SYSDATE,'NEW','214-555-1212','6004 Somewhere','111','DIRECT');
  insert into order_audit values (SYSDATE,'CXL','214-555-1212','6004 Somewhere','222','DIRECT');
  insert into order_audit values (SYSDATE,'NEW','214-555-1212','6004 Somewhere','222','DEALER');
  commit;

 

Now log in as the user ORDER_AUDIT and output the file to CEF format

 

-- Schema owned by order_audit
-- Called using " EXEC Order_audit_proc('SEP082009','SEP092009','c:\cefout','SEP082009.TXT');"
CREATE OR REPLACE PROCEDURE Order_audit_proc  (P_PERIOD_START in VARCHAR2, P_PERIOD_STOP IN VARCHAR2,
              p_directory   in varchar2, p_cef_file_name   in varchar2) is
--  Devine Variables to control application
    CEF_HDR   varchar2(3000) default 'CEF:0|CorpX|Orders|4.0.0.5276.1|';
V_LOOP_COUNT    NUMBER;        line_size       varchar2(1023);
    V_PERIOD_START  DATE;        V_PERIOD_STOP   DATE;
    V_buffer_size  number(5);       v_severity  varchar2(3000)  default '1';
    f     utl_file.file_type;
-- Define Variables to hold the row processed
v_name   varchar2(3000)  default ''; v_activity_code varchar2(3000)  default '';
    v_cust_phone varchar2(3000)  default ''; v_cust_addr  varchar2(3000)  default '';
    v_order_num  varchar2(3000)  default ''; v_order_src  varchar2(3000)  default '';
v_event_id     varchar2(3000)  default '100';
begin
    V_PERIOD_START  := TO_DATE(P_PERIOD_START,'MONDDYYYY'); V_PERIOD_STOP   := TO_DATE(P_PERIOD_STOP, 'MONDDYYYY');
    f := utl_file.fopen(p_directory,p_cef_file_name,'W',32760); -- Open the file to accept the CEF output
V_LOOP_COUNT := 0;
    for s in (select activity_code,cust_phone,cust_addr,order_num,order_src
     from  order_audit.order_audit where end_time > V_PERIOD_START and   end_time < V_PERIOD_STOP)
loop
        V_LOOP_COUNT   :=V_LOOP_COUNT + 1;        V_activity_code  :=s.activity_code||'|';
        V_cust_phone  :=s.cust_phone||'|';       V_cust_addr   :=s.cust_addr||'|';
        V_order_num   :=s.order_num||'|';        V_order_src   :=s.order_src||'|';
-- If each value is not null then output the value with the correct tag, if not then set to blank
        if s.ACTIVITY_CODE is not null then V_ACTIVITY_CODE := ' ACT='||s.ACTIVITY_CODE; ELSE V_ACTIVITY_CODE := ''; end if;
        if s.CUST_PHONE    is not null then V_CUST_PHONE  := ' CS1='||s.CUST_PHONE;  ELSE V_CUST_PHONE  := ''; end if;
        if s.CUST_ADDR     is not null then V_CUST_ADDR  := ' CS2='||s.CUST_ADDR;  ELSE V_CUST_ADDR   := ''; end if;
        if s.ORDER_NUM     is not null then V_ORDER_NUM  := ' CN1='||s.ORDER_NUM;  ELSE V_ORDER_NUM   := ''; end if;
        if s.ORDER_SRC     is not null then V_ORDER_SRC  := ' CS3='||s.ORDER_SRC;  ELSE V_ORDER_SRC   := ''; end if;
line_size := length(CEF_HDR ||''||V_name    ||''||V_severity ||''||V_activity_code
       ||''||V_cust_phone          ||''||V_cust_addr   ||''||V_order_num     ||''||V_order_src);
-- dbms_output.put_line(line_size||'  bytes written to the output file'); --Debug for feedback
if line_size < 32000 then
        utl_file.put_line(f,CEF_HDR
     ||''||V_name           ||''||V_severity     ||'|'||V_event_id  ||''||V_ACTIVITY_CODE 
     ||''||V_CUST_PHONE     ||''||V_CUST_ADDR    ||''||V_ORDER_NUM  ||''||V_ORDER_SRC);
else
dbms_output.put_line(line_size||'  max buffer size was exceeded');
end if;
end loop;
utl_file.fclose(f); -- Close the file
DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS WAS:  '||V_LOOP_COUNT); -- print session summary
DBMS_OUTPUT.PUT_LINE('FILE WRIITEN START DATE:  '||V_PERIOD_START);
DBMS_OUTPUT.PUT_LINE('FILE WRIITEN STOP  DATE:  '||V_PERIOD_STOP);
EXCEPTION
  WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR (-26502, 'CONVERSION OR SIZE-CONSTRAINT-ERROR');
  WHEN utl_file.invalid_mode   THEN    RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
  WHEN utl_file.invalid_path   THEN    RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
  WHEN utl_file.invalid_filehandle  THEN    RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
  WHEN utl_file.invalid_operation  THEN    RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
  WHEN utl_file.read_error    THEN    RAISE_APPLICATION_ERROR (-20055, 'Read Error');
  WHEN utl_file.internal_error   THEN    RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
  WHEN utl_file.charsetmismatch  THEN    RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR But Later I/O Inconsistent');
  WHEN utl_file.file_open    THEN    RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
  WHEN utl_file.invalid_maxlinesize THEN    RAISE_APPLICATION_ERROR (-20060,'Line Size Exceeds 32K');
  WHEN utl_file.invalid_filename  THEN    RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
  WHEN utl_file.access_denied   THEN    RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
  WHEN utl_file.invalid_offset   THEN    RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
  WHEN others       THEN    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
/

-- Expected Output c:\cefout\SEP082009.TXT
-- CEF:0|CorpX|Orders|4.0.0.5276.1|1|100 ACT=NEW CS1=214-555-1212 CS2=6004 Somewhere CN1=111 CS3=DIRECT
-- CEF:0|CorpX|Orders|4.0.0.5276.1|1|100 ACT=CXL CS1=214-555-1212 CS2=6004 Somewhere CN1=222 CS3=DIRECT
-- CEF:0|CorpX|Orders|4.0.0.5276.1|1|100 ACT=NEW CS1=214-555-1212 CS2=6004 Somewhere CN1=222 CS3=DEALER

 

 

 

Brian Wolff, Principal Sales Engineer, ArcSight; Jon Inns    , Sales Engineer, ArcSight

Attachments

    Outcomes