How To Find Users Who Tried To Login To Database.

############################################################################
Below Script Used To Fetched The Report Of Users Who Tried But Failed To Login To Database in CSV Format 
############################################################################

-- -----------------------------------------------------------------------------------
-- Author         : Ashwani Kumar
-- Description  : Failed Login Notification.
-- Requirements : Access to the v$views.
-- Last Modified: 09/Oct/2017
-- -----------------------------------------------------------------------------------

set markup html on
set echo on
set feedback off
set serverout off
set feedback off

define file_time=date
column tm new_value file_time
select 'Notification_DBNAME_'||SYSDATE||'.xls' tm from dual;

spool C:\&file_time

Select name, open_mode, database_role from v$database;

set echo off
prompt ***********************FAILED LOGIN ATTEMPT*************************************

variable KEY1 NUMBER;
exec :KEY1 := '&NUMBER_OF_DAYS';
col action_name format a10
SELECT
  TO_CHAR(TIMESTAMP,'DD-MON-YYYY hh24:mi') LOGIN_DATE,
  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
  SUBSTR(USERNAME,1,20) USERNAME,
  SUBSTR(TERMINAL,1,20) TERMINAL,
  ACTION_NAME,
  DECODE(RETURNCODE,0,'SUCCESS',1017,'FAILED LOGIN',28000,'ACCOUNTLOCKED AFTER FAILED LOGIN') AS Message
FROM
  SYS.DBA_AUDIT_SESSION
WHERE
  TIMESTAMP BETWEEN SYSDATE-&days AND SYSDATE and returncode <> 0
ORDER BY TIMESTAMP ASC ;

spool off

set markup html off