Listener log data mining with SQL

Listener log data mining with SQL


ref .. 
https://connor-mcdonald.com/2019/12/16/listener-log-data-mining-with-sql/

https://www.pythian.com/blog/technical-track/how-to-make-an-in-database-listenerlog-file



https://anuj-singh.blogspot.com/2012/01/oracle-how-to-get-only-file-name-with.html


CREATE OR REPLACE DIRECTORY listener_log_xml AS '/u01/app/grid/diag/tnslsnr/irac02/listener/alert';


DROP TABLE listener_log_xml;

CREATE TABLE listener_log_xml (line VARCHAR2(4000))
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY listener_log_xml
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    NOBADFILE
    NOLOGFILE
    NODISCARDFILE
    FIELDS LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      line CHAR(4000)
    )
  )
  LOCATION ('log.xml')
)
REJECT LIMIT UNLIMITED
/
 
 
 
SELECT * FROM listener_log_xml WHERE line LIKE '%PORT%' AND rownum <= 100
 
 
 
 
SET LONG 999999999     PAGESIZE 100

select line  from (SELECT line FROM listener_log_xml 
WHERE 1=1
-- and  rownum <= 10000
)
where 1=1
--and REGEXP_LIKE (line,'%PORT%') 
and line LIKE '%PORT%'

/

 
 
--for IP 

SET PAGESIZE 200
COLUMN host FORMAT a30

SELECT DISTINCT host
FROM
  ( SELECT SUBSTR(host, 1, INSTR(host, ')')-1) AS host
    FROM
      ( SELECT 
          -- CASE WHEN line LIKE '%HOST=%' THEN SUBSTR(line, INSTR(line, 'HOST=', -1, 1)+5) END host
          SUBSTR(line, INSTR(line, 'HOST=', -1, 1)+5) AS host
        FROM listener_log_xml
        WHERE line LIKE '%PORT%'
      )
  )
ORDER BY 1
/
 
 
 
 
--for port 

SET PAGESIZE 200
COLUMN host FORMAT a30

SELECT DISTINCT host
FROM
  ( SELECT SUBSTR(host, 1, INSTR(host, ')')-1) AS host
    FROM
      ( SELECT 
          -- CASE WHEN line LIKE '%HOST=%' THEN SUBSTR(line, INSTR(line, 'HOST=', -1, 1)+5) END host
          SUBSTR(line, INSTR(line, 'PORT=', -1, 1)+5) AS host
        FROM listener_log_xml
        WHERE line LIKE '%PORT%'
      )
  )
ORDER BY 1
/
 
 
 
 
 
 
ww
SET LONG 999999999     PAGESIZE 100

select distinct substr(line,instr(line,'HOST',-1)-1)  from (SELECT line FROM listener_log_xml 
WHERE 1=1
-- and  rownum <= 10000
)
where 1=1
--and REGEXP_LIKE (line,'%PORT%') 
and line LIKE '%PORT%'

/