Mining listener log

create directory LISTENER_LOG_DIR as '/app/oracle/product/9.2.0/network/log'

CREATE TABLE full_listener_log(  line VARCHAR2(4000))ORGANIZATION EXTERNAL (
  TYPE oracle_loader  DEFAULT DIRECTORY listener_log_dir  ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE NOBADFILE NOLOGFILE NODISCARDFILE) 
  LOCATION ('lsnr_aol_pro.log')
)REJECT LIMIT UNLIMITED   

select * from full_listener_log

CREATE TABLE listener_log(
  log_date varchar2(30), connect_string VARCHAR2(300), protocol_info VARCHAR2(300), action VARCHAR2(15), 
  service_name VARCHAR2(15),   return_code NUMBER(10)
)ORGANIZATION EXTERNAL (   
  TYPE oracle_loader DEFAULT DIRECTORY listener_log_dir ACCESS PARAMETERS (      
    RECORDS DELIMITED BY NEWLINE NOBADFILE NOLOGFILE NODISCARDFILE FIELDS TERMINATED BY "*" LRTRIM MISSING FIELD VALUES ARE NULL (          
    log_date CHAR(20), connect_string, protocol_info, action, service_name, return_code)   
  ) LOCATION ('lsn_perfrete.log')
)REJECT LIMIT UNLIMITED

create or replace function parse_listener_log_line(p_in varchar2, p_param in varchar2)
return varchar2
as
    l_begin     number(3);
    l_end       number(3);
    l_val       varchar2(2000);
begin
    if p_param not in (
            'SID',
            'SERVICE_NAME',
            'PROGRAM',
            'SERVICE',
            'HOST',
            'USER',
            'PROTOCOL',
            'TYPE',
            'METHOD',
            'RETRIES',
            'DELAY',
            'PORT',
            'COMMAND'
    ) then
        raise_application_error (-20001,'Invalid Parameter Value '||p_param);
    end if;
    l_begin := instr (upper(p_in), '('||p_param||'=');
    l_begin := instr (upper(p_in), '=', l_begin);
    l_end := instr (upper(p_in), ')', l_begin);
    l_val := substr (p_in, l_begin+1, l_end - l_begin - 1);
    return l_val;
end;

select host, ip, count(*) from (
  select parse_listener_log_line(connect_string,'HOST') host, parse_listener_log_line(protocol_info,'HOST')ip
  , l.*
  from listener_log l
) a
where not(host is null and ip is null)
group by host, ip
order by 3 desc