Failed Login Attempts
Derived from Doc ID 352389.1.
Get Time of Event
-- Get Time of Event: Today SELECT to_char(TIMESTAMP, 'YYYY-mm-dd HH12:MI:SS') FROM dba_audit_trail WHERE returncode=1017 AND TIMESTAMP > sysdate-1; -- Get Time of Event: Last 6 Hours SELECT to_char(TIMESTAMP, 'YYYY-mm-dd HH12:MI:SS') FROM dba_audit_trail WHERE returncode=1017 AND TIMESTAMP > SYSDATE-(6/24); -- Get Time of Event: Last Hour SELECT to_char(TIMESTAMP, 'YYYY-mm-dd HH12:MI:SS') FROM dba_audit_trail WHERE returncode=1017 AND TIMESTAMP > SYSDATE-(1/24); -- Get Time of Event: Last 15 min SELECT to_char(TIMESTAMP, 'YYYY-mm-dd HH12:MI:SS') FROM dba_audit_trail WHERE returncode=1017 AND TIMESTAMP > SYSDATE-(15/1440);
Event Detail Example
-- users.FailedLoginAttempts.sql -- Purpose: Show failed login attempts.
set verify off; set linesize 240 set pagesize 9999 COL username FORMAT a15 HEAD 'Oracle_User' COL os_username FORMAT a15 HEAD 'OS_User' COL userhost FORMAT a20 HEAD 'Host' COL client_id FORMAT a20 HEAD 'Client_ID' COL timestamp FORMAT a20 HEAD 'Timestamp' ACCEPT v_Days PROMPT 'Days [1]: ' DEFAULT 1 PROMPT alter session set NLS_TIMESTAMP_FORMAT ='YYYY-MM-DD HH:MI:SS.FF'; SELECT username, os_username, userhost, client_id, trunc(timestamp) "Timestamp", count(*) "Failed_Logins" FROM dba_audit_trail WHERE returncode=1017 AND --1017 is invalid username/password TIMESTAMP > sysdate-&v_Days GROUP BY username, os_username, userhost, client_id, trunc(timestamp);