-- Listener.log Analyse
-- v 0.0.1
--
-- required privileges:
-- 	CREATE ANY DIRECTORY
-- 	DROP ANY DIRECTORY
-- 	CREATE PROCEDURE
-- 	DROP PROCEDURE
-- 	CREATE TABLE
-- 	DROP TABLE


-----------
spool listener_analysis.txt

prompt set SQL*Plus Parameter
set echo on
set pagesize 0
set linesize 150


prompt get OS INFO
host date /t
host time /t
host set
-- UNIX:  host time


prompt get DB info
-- Database timestamp
select to_char(sysdate,'YYYY-MM-DD HH24-MI') from dual;

-- Patchset / Security Patches
select * from v$version;
select * from dba_registry_history;

-- installed components
select * from dba_registry;

-- Database information
select * from v$instance;
select * from v$database;



prompt create directory
create directory LISTENER_LOG_DIR as 'C:\oracle\ora102\NETWORK\log';

prompt create external table
create table listener_log (
   log_date date,
   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(30) date_format 
          date mask "DD-MON-YYYY HH24:MI:SS",
          connect_string,
          protocol_info,
          action,
          service_name,
          return_code
      )    )
   location ('listener.log')
)
reject limit unlimited
/

prompt create parse_function
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;
 /


prompt Run queries against listener.log
col program format a70
col cnt format 999,999

prompt different programs per SID/Servicename
select lower(service_name) Service, parse_listener_log_line(connect_string,'PROGRAM') program, count(1) cnt
from listener_log
group by service_name, parse_listener_log_line(connect_string,'PROGRAM')
order by 1, 3 desc, 2;
-----------

prompt different usernames
select lower(service_name) Service,substr(parse_listener_log_line(connect_string,'USER'),1,70) USERNAME, count(1) cnt
from listener_log
group by service_name, parse_listener_log_line(connect_string,'USER')
order by 1, 3 desc, 2;
----

prompt what user has used what program how often
select lower(service_name) Service, substr(parse_listener_log_line(connect_string,'USER'),1,40) USERNAME, substr(parse_listener_log_line(connect_string,'PROGRAM'),1,60) PROGRAM, 
count(1) cnt
from listener_log
group by service_name, parse_listener_log_line(connect_string,'USER'), parse_listener_log_line(connect_string,'PROGRAM')
order by 1, 2, 3;
--

--
prompt Usage of export utilities
select lower(service_name) Service, substr(parse_listener_log_line(connect_string,'USER'),1,40) USERNAME, substr(parse_listener_log_line(connect_string,'PROGRAM'),1,60) PROGRAM, 
count(1) cnt
from listener_log
where lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%rman%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%exp%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%expdp%'
group by service_name, parse_listener_log_line(connect_string,'USER'), parse_listener_log_line(connect_string,'PROGRAM')
order by 1, 2, 3;

--
prompt Usage of listener command
--
select substr(parse_listener_log_line(connect_string,'COMMAND'),1,40) COMMAND, 
count(1) cnt
from listener_log
group by parse_listener_log_line(connect_string,'COMMAND') 
order by 2, 1;

prompt Usage per day
--
select to_char(log_date,'YYYY-MM-DD') mydate, count(*) cnt
from listener_log
group by to_char(log_date,'YYYY-MM-DD')
order by 1, 2;
----

prompt Usage per hour
--
select to_char(log_date,'HH24') mydate, count(*) cnt
from listener_log
group by to_char(log_date,'HH24')
order by 1, 2;
----
prompt cleanup
--drop function parse_listener_log_line;
--drop table listener_log;
--drop directory LISTENER_LOG_DIR;

-- terminate session
--exit

-- disable spooling
spool off
---up
--drop function parse_listener_log_line;
--drop table listener_log;
--drop directory LISTENER_LOG_DIR;

-- terminate session
--exit

-- disable spooling
spool off
---