-- Listener.log Analyse
-- v 0.9 - first public release
-- (c) 2007-2009 by Red-Database-Security GmbH
--
-- required privileges:
-- 	CREATE ANY DIRECTORY
-- 	DROP ANY DIRECTORY
-- 	CREATE PROCEDURE
-- 	DROP PROCEDURE
-- 	CREATE TABLE
-- 	DROP TABLE


-----------
spool listener_single_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 Run queries against listener.log
col program format a70
col cnt format 999,999

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

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

prompt what user has used what program how often
select 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 parse_listener_log_line(connect_string,'USER'), parse_listener_log_line(connect_string,'PROGRAM')
order by 1, 2;
--

--
prompt Usage of export utilities
select 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  parse_listener_log_line(connect_string,'USER'), parse_listener_log_line(connect_string,'PROGRAM')
order by 1, 2;



-- usage of security / hacker tools

select  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 '%ngssqu%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%appdet%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%repscan%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%checkpwd%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%sidguess%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%coss%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%goss%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%orabrute%'
or lower(parse_listener_log_line(connect_string,'PROGRAM')) like '%oscan%'
group by 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


-- disable spooling
spool off
---m listener_log
group by to_char(log_date,'HH24')
order by 1, 2;
----
prompt cleanup


-- disable spooling
spool off
---