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);