RDBMS-- Parsing the listener log with a straight forward in-database method / by taking that jdbc_url anomaly into consideration :)

Remember; years ago, I shared a method for reading the listener log files in a scriptized way.. This type of a method can be used during security checks, or during a migration project (to see the connections, to know by which applications the connections to the database are made..) 

I want to remind you again; in migration projects, this type of an analysis makes our job easier especially when bundling .(when deciding the migration bundles)

You can review that blog post -> https://ermanarslan.blogspot.com/2016/03/rdbms-listener-logs-jdbc-parsing.html

Today, I give you another method for reading and parsing the listener log files. This one is purely an in-database method, and it is pretty straight forward.

I want to mention that, we handle the listener log lines which include jdbc_url correctly.. As you may already know; HOST parameter in the CONNECT_STRING shows __jdbc__ when the client connects to the database using the Jdbc thin driver. So, in order to determine the the real host name of these jdbc clients, we should use the info recorded in the PROTOCOL_INFO.

Let's see;

First, we create a database directory to reach the listener trace/log file from the database..

create or replace directory LISTENER_LOG as '/u01/dbebs/PROD/db/tech_st/11.2.0/admin/PROD_ebstestdb/diag/tnslsnr/ebstestdb/prod/trace';

We create an external table to read the listener log file using the database directory we just created. (we use our listener log file name in the location caluse..)

create table listener_log_ea ( line varchar2(4000))organization external (type oracle_loader default directory LISTENER_LOG access parameters (records delimited by newline nobadfile nologfile nodiscardfile fields ldrtrim missing field values are null reject rows with all null fields (line char (40000)))location ('prod.log'))reject limit unlimited;

 Lastly we query the external table by taking that jdbc anomaly into consideration :)

with details as
(
select last_value(tstamp ignore nulls) over ( order by tstamp ) as tstamp,
substr(host,1,instr(host,')')-1) host,
substr("USER",1,instr("USER",')')-1) "USER"
from
( select
case when line like '__-___-____ __:__:__ %' then to_date(substr(line,1,20),'DD-MON-YYYY HH24:MI:SS') end tstamp,
case when line like '%HOST=__jdbc__%' then substr(line,instr(line,'(ADDRESS=(PROTOCOL=tcp)(HOST=')+29) else
case when line like '%HOST=%' then substr(line,instr(line,'HOST=')+5) end
end host,
case when line like '%USER=%' then substr(line,instr(line,'USER=')+5) end "USER"
from listener_log_ea
)
)
select *
from details where host is not null

That's it.. That "with" query can be modified according to the needs and the characteristics of the environment.. After all, you got the point..