Script consists of a cron script and an sql script on the host server.
failed_logins.sh
#!/bin/tcsh -f
source ~oracle/.cshrc
setenv VDATE `date '+%m/%d/%y %A %X'`
setenv MAILGRP "email@company.com"
setenv scrpt [script location]
sqlplus -SILENT user/password@ORCL < ${scrpt}/failed_logins.sql
cat failed_logons.out | /bin/mailx -s "${ORACLE_SID} - `uname -n` : Logins attempted Report for $VDATE" \
$MAILGRP
exit
SQLPLUS script
failed_logins.sql
set termout off
spool failed_logons.out
set linesize 232 tab off trimspool on
set pagesize 105
set pause off
set echo on
set feedb off
set heading off
column "EVENT_TIMESTAMP" format a40
column "OS_USERNAME" format a15
column "USERHOST" format a15
column "DBUSERNAME" format a10
column "CLIENT_PROGRAM_NAME" format a35
column "ACTION_NAME" format a10
column "UNIFIED_AUDIT_POLICIES" format a20
select 'Login Report for '||TO_CHAR(sysdate,'DAY DD-MON-YYYY HH24:MI:SS') "Title" from dual
/
select 'EVENT_TIMESTAMP------------------OS_USERNAME--USERHOST---DBUSERNAME--CLIENT_PROGRAM_NAME----ACTION_NAME--POLICY' from dual;
SELECT TO_CHAR(event_timestamp,'YYYY-MM-DD HH24:MI:SS') EVENT_TIMESTAMP,
os_username OS_USERNAME,
userhost USERHOST,
dbusername DBUSERNAME,
client_program_name CLIENT_PROGRAM_NAME,
action_name ACTION_NAME,
unified_audit_policies UNIFIED_AUDIT_POLICIES
FROM unified_audit_trail
where action_name = 'LOGON'
and unified_audit_policies = 'ORA_LOGON_FAILURES'
and trunc(event_timestamp) > trunc(sysdate)-10
ORDER BY event_timestamp desc
/
spool off
No comments:
Post a Comment