select
decode((count(trigger_name)),0,'LOGON trigger missing',
'Number of logon triggers: ' || count(trigger_name) ) "INFO"
from sys.dba_triggers
where
TRIGGERING_EVENT like 'LOGON%'
and status='ENABLED'
and owner='SYS'
union
select
decode((count(trigger_name)),0,'LOGOFF trigger missing','',
'Number of logoff triggers:' || count(trigger_name)) "INFO"
from sys.dba_triggers
where
TRIGGERING_EVENT like 'LOGOFF%'
and status='ENABLED'
and owner='SYS'


The field TRIGGERING_EVENT could have the spaces at the end! Very clever…

Create the table to save logon information:


CREATE TABLE logonaudittable (
event VARCHAR2(10),
sid NUMBER,
serial# NUMBER,
timestamp DATE,
username VARCHAR2(30),
osuserid VARCHAR2(30),
machinename VARCHAR2(64)
);


Continue reading →