Script to Monitor and Report Audit Trail Records in sys.aud$

 

Script Description:

This script monitors the major audit trail records and failed login attempts, it can be easily deployed and customized. So far, it has been tested on Linux Environment.

Script Category: Auditing & Security

How it works:

You have to schedule this script to run in the OS scheduler "crontab", you can decide how fast you should receive a notification whenever a new "major" audit event get created. Then set the same schedule interval in minutes inside the script to determine the window of time which the script will fetch the data from sys.aud$.

e.g. If you schedule the script to run every 30 minutes in the crontab, you should set the Threshold "MINUTES" inside the scripts to 30.

The script uses sendmail as the only notification method, so you have to set the following parameter replacing the pattern youremail@yourcompany.com to your actual Email:

EMAIL="youremail@yourcompany.com"

In addition, the script has many parameters to help you narrow down the scope of audit actions monitoring, you can change the values in green color:

 HTMLENABLE=Y      # Enable HTML Email Format [Default Enabled].
 RECORDSNUM=1     # Send an Email if the sum of audit records >= the threshold [Default 1 record].
 REPORT_FAILED_LOGINS=Y    # Enable the reporting of failed login attempts. [Default Enabled].
 REPORT_AUDIT_RECORDS=Y  # Enable the reporting of audit records [Default Enabled].
 EXCLUDE_DBUSERS="'dba_bundleexp7'"  # Exclude DB user from reporting their activities [In lowercase]. e.g. EXCLUDE_DBUSERS="'sys','scott'"
 EXCLUDE_OSUSERS="'user1'"    # Exclude OS user from reporting their activities [In lowercase]. e.g. EXCLUDE_OSUSERS="'oracle','grid'"
 EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT'" # Exclude specific AUDIT EVENTS from reporting.

EXCLUDE_ACTIONS is an important parameter to help you exclude specific audit actions from getting reported. This reporting mechanism should cover only crucial activities that need immediate attention like DROP, TRUNCATE, ALTER, ... etc for example; minor activities like Log In, Log Off and SELECT shouldn't be in the scope of such monitoring mechanism; otherwise you will get your mailbox filled unnecessarily.

I've already excluded common audit actions from the scope of getting reported like:

SELECT, SET ROLE, LOGON, LOGOFF, LOGOFF BY CLEANUP, EXPLAIN, PL/SQL EXECUTE, SYSTEM AUDIT

Let's suppose you want to exclude DML's from getting reported; modify the parameter as follows:

EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT','INSERT','UPDATE','DELETE','SESSION REC'"

Ensure that you enclose each audit action between "single quotation" '' and separating between them using "comma" ,

To display all the available actions names that you can use for that parameter, run this statement:

SQL> select distinct action_name from dba_audit_trail order by 1;  

Feel free to add more actions to be excluded to EXCLUDE_ACTIONS parameter, and I encourage you to do so.

Precautions:

The last and the most important remaining point here is indexing sys.aud$ table:

Your audit trail table sys.aud$ may have millions/billions of rows, querying this table frequently can degrade your database performance and most probably it can bring the server on its knees; especially if it has humble resources!

If reporting audit records is important for you, then you must create the following index before start using this script and to avoid any performance degradation it can cause by this script: (Create it with online option to avoid getting the DB hung)

SQL> CREATE INDEX sys.idx_ntimestamp# ON sys.aud$(ntimestamp#) ONLINE;
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS', tabname => 'AUD$', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

When the script run, it will check if sys.aud$ (ntimestamp#) is indexed, if it's not; it will display a warning message along with the recommended index creation statement to execute.

It is worth to mention that Oracle doesn't recommend creating any indexes on sys.aud$ table as it may degrade the performance of record insertions to sys.aud$, in addition to other minor reasons mentioned in this Note: The Effect Of Creating Index On Table Sys.Aud$ (Doc ID 1329731.1)

From my experience; I use this script along with the creation of the above index on very busy OLTP databases without noticing any performance degradation. Creating an index on sys.aud$ table is like creating an index on any other "busy table"; Yes it will add an extra overhead (to maintain the index) when audit records are inserted, but still Oracle can handle this efficiently.

Indeed, you are the right one to decide whether adding an index to sys.aud$ table will degrade the performance or not; based on the daily volume of audit data that get inserted to sys.aud$, along with the hardware resources of your DB server.

One last thing, you may ask why I coded the script to select directly from sys.aud$ instead of using DBA_AUDIT_TRAIL; the answer is that I've noticed in many cases that selecting from DBA_AUDIT_TRAIL will force the optimizer to not use the above mentioned indexes leading to an expensive full table scans, this is why I avoided using it.

Please feel free to share your thoughts and suggestions.

To download the script:

https://www.dropbox.com/s/vja3hptpzy7a3in/monitor_audit_records.sh?dl=0

After you click on that link, click on the down arrow at the top-right side of the page:

GitHub Version:

	# #####################################################################################################################################################
	# Monitor audit records [Failed login attempts & MAJOR AUDIT RECRODS] on audit trail table
	VER="[1.0]"
	SCRIPT_NAME="monitor_audit_records"
	# #####################################################################################################################################################
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 28-09-20 # # # # #
	#
	#
	#
	#
	#
	# ######################################################################################################################################################
	EMAIL="youremail@yourcompany.com"
	# #########################
	# THRESHOLDS:
	# #########################
	# Modify the THRESHOLDS to the value you prefer:
	HTMLENABLE=Y # Enable HTML Email Format [Default Enabled].
	MINUTES=10 # Check audit records in the last N number of minutes. [Default 5 minutes].
	RECORDSNUM=1 # Send an Email if the sum of audit records >= the threshold. [Default 1 record].
	REPORT_FAILED_LOGINS=Y # Enable the reporting of failed login attempts. [Default Enabled].
	REPORT_AUDIT_RECORDS=Y # Enable the reporting of audit records. [Default Enabled].
	EXCLUDE_DBUSERS="'dba_bundleexp7'" # Exclude DB user from reporting their activities [In lowercase]. e.g. EXCLUDE_DBUSERS="'sys','scott'"
	EXCLUDE_OSUSERS="'user1'" # Exclude OS user from reporting their activities [In lowercase]. e.g. EXCLUDE_OSUSERS="'oracle','grid'"
	EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT'" # Exclude AUDIT EVENTS from reporting
	# e.g. To exclude all previous Audit Events along with DMLs (inserts, updates, deletes):
	# EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT','SESSION REC'"
	# To explore all the current available events in your DB run:
	# SQL> select distinct action_name from dba_audit_trail;
	SQLLINESIZE=200 # The LINE SIZE for SQLPLUS outputs.
	OSLINESIZE=300 # The LINE SIZE for OS Commands outputs. [Default is 167]
	SENDER="AUDITRECORDS" # Change the Email sender name. e.g. EXCLUDE_DBUSERS="AUDITRECORDS"
	# #######################################
	# Excluded INSTANCES:
	# #######################################
	# Here you can mention the instances the script will not run against:
	# Use pipe "|" as a separator between each instance name.
	# e.g. Excluding: -MGMTDB, ASM and APX instances:
	EXL_DB="\-MGMTDB|ASM|APX"
	# #######################################
	# Verify Variables:
	# #######################################
	export HTMLENABLE
	export MINUTES
	export RECORDSNUM
	export REPORT_FAILED_LOGINS
	export REPORT_AUDIT_RECORDS
	export EXCLUDE_DBUSERS
	export EXCLUDE_OSUSERS
	export EXCLUDE_ACTIONS
	export SQLLINESIZE
	export OSLINESIZE
	export EXL_DB
	case ${EXCLUDE_DBUSERS} in
	"") export HASHDBUSERNAME="--";;
	*) export HASHDBUSERNAME="";;
	esac
	case ${EXCLUDE_OSUSERS} in
	"") export HASHOSUSERNAME="--";;
	*) export HASHOSUSERNAME="";;
	esac
	case ${EXCLUDE_ACTIONS} in
	"") export HASHACTIONNAME="--";;
	*) export HASHACTIONNAME="";;
	esac
	export SRV_NAME="`uname -n`"
	# Check if MAIL_LIST parameter is not set notify the user and exit:
	case ${EMAIL} in "youremail@yourcompany.com")
	echo
	echo "******************************************************************"
	echo "Buddy! You forgot to edit line# 50 in ${SCRIPT_NAME}.sh script."
	echo "Please replace youremail@yourcompany.com with your E-mail address."
	echo "******************************************************************"
	echo
	echo "Script Terminated !"
	echo
	exit;;
	esac
	# Check if there is another session of the script is running: [Avoid performance impact]
	RUNCOUNTT=`ps -ef|grep -v grep|grep -v vi|grep ${SCRIPT_NAME}|wc -l`
	if [ ${RUNCOUNTT} -gt 2 ]
	then
	echo -e "\033[32;5m${SCRIPT_NAME}.sh script is currently running by another session.\033[0m"
	echo ""
	echo "Please make sure the following sessions are completed before running dbalarm script: [ps -ef|grep -v grep|grep -v vi|grep ${SCRIPT_NAME}]"
	ps -ef|grep -v grep|grep -v vi|grep ${SCRIPT_NAME}.sh
	echo "Script Terminated !"
	echo
	exit
	fi
	export MAIL_LIST="${EMAIL}"
	#export MAIL_LIST="-r ${SRV_NAME} ${EMAIL}"
	echo
	echo "[${SCRIPT_NAME} Script Started ...]"
	echo
	# Verify log location:
	LOGDIR=/tmp
	if [ ! -w "${LOGDIR}" ]; then
	LOGDIR=~
	fi
	# ###########################
	# Check the Linux OS version:
	# ###########################
	export PATH=${PATH}:/usr/local/bin
	FILE_NAME=/etc/redhat-release
	export FILE_NAME
	if [ -f ${FILE_NAME} ]
	then
	LNXVER=`cat /etc/redhat-release | grep -o '[0-9]'|head -1`
	export LNXVER
	fi
	# Run the script on each DB:
	for ORACLE_SID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	do
	export ORACLE_SID
	# ###################
	# Getting ORACLE_HOME:
	# ###################
	ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v "\-MGMTDB"|awk '{print $1}'|tail -1`
	USR_ORA_HOME=`grep -i "^${ORA_USER}:" /etc/passwd| cut -f6 -d ':'|tail -1`
	# SETTING ORATAB:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	export ORATAB
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	export ORATAB
	fi
	# ATTEMPT1: Get ORACLE_HOME using pwdx command:
	PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
	export PMON_PID
	ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
	export ORACLE_HOME
	# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	## If OS is Linux:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	fi
	fi
	# ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`dbhome "${ORACLE_SID}"`
	export ORACLE_HOME
	fi
	# ATTEMPT4: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
	export ORACLE_HOME
	fi
	# ATTEMPT5: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' ${USR_ORA_HOME}/.bash_profile ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
	export ORACLE_HOME
	fi
	# ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	if [ -x /usr/bin/locate ]
	then
	ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
	export ORACLE_HOME
	fi
	fi
	# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
	echo "e.g."
	echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
	exit
	fi
	printf "`echo "Reporting AUDIT records on Database ["` `echo -e "\033[33;5m${ORACLE_SID}\033[0m"` `echo "]"`\n"
	echo ""
	# ###################
	# WARNINGS SECTION:
	# ###################
	# Display a WARNING message if AUDITING is not enabled:
	AUDCOUNTRAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	prompt
	SELECT COUNT(*) FROM V\$PARAMETER WHERE NAME='audit_trail' AND VALUE='NONE';
	exit;
	EOF
	)
	AUDCOUNT=`echo ${AUDCOUNTRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
	if [ ${AUDCOUNT} -ge 1 ]
	then
	echo ""
	printf "`echo -e "\033[33;5mINFO!\033[0m"` `echo " AUDITING IS NOT ENABLED ON DATABASE [${ORACLE_SID}] ..."`\n"
	echo ""
	fi
	# Display a WARNING message if NTIMESTAMP# column in sys.aud$ is not indexed:
	INDEXCOUNTRAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	prompt
	select count(*) from dba_ind_columns where table_owner='SYS' and table_name='AUD\$' and column_name='NTIMESTAMP#';
	exit;
	EOF
	)
	INDEXCOUNT=`echo ${INDEXCOUNTRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
	if [ ${INDEXCOUNT} -le 0 ]
	then
	echo ""
	printf "`echo -e "\033[33;5mWARNING:\033[0m"` `echo " NTIMESTAMP# Column in sys.aud$ table is"` `echo -e "\033[33;5mNOT INDEXED\033[0m"`\n"
	echo ""
	echo "This script may cause a performance degradation when it run!"
	echo "In order to avoid execution slowness, create an index on aud$ (NTIMESTAMP#) column by executing this CREATE INDEX statement:"
	echo ""
	echo "CREATE INDEX sys.idx_ntimestamp# ON sys.aud\$(ntimestamp#) ONLINE;"
	echo "EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS', tabname => 'AUD\$', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);"
	echo ""
	echo ""
	export NOINDEXWARNING="PROMPT"
	export NOINDEXWARNING1="PROMPT RECOMMENDATION: sys.aud$ table should be INDEXED to speed up this script using these statements:"
	export NOINDEXWARNING2="PROMPT CREATE INDEX sys.idx_ntimestamp# ON sys.aud\$(ntimestamp#) ONLINE;;"
	export NOINDEXWARNING3="PROMPT EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS', tabname => 'AUD$', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);;"
	sleep 2
	fi
	# ###################
	# Check failed logins:
	# ###################
	case ${REPORT_FAILED_LOGINS} in
	y|Y|yes|YES|Yes|ON|On|on)
	FAILEDLOGINCOUNTRAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	prompt
	select count(*) from aud\$
	where ntimestamp# >= new_time(sysdate - ${MINUTES}/1440,'gmt','edt')
	and action# between 100 and 102
	and returncode = 1017
	${HASHDBUSERNAME} and lower (USERID) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (SPARE1) not in (${EXCLUDE_OSUSERS})
	/
	/*
	select count(*) from DBA_AUDIT_SESSION
	where returncode = 1017
	and timestamp >= (sysdate - ${MINUTES}/1440)
	${HASHDBUSERNAME} and lower (USERNAME) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (OS_USERNAME) not in (${EXCLUDE_OSUSERS})
	/
	*/
	exit;
	EOF
	)
	FAILEDLOGINCOUNT=`echo ${FAILEDLOGINCOUNTRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
	if [ ${FAILEDLOGINCOUNT} -ge ${RECORDSNUM} ]
	then
	echo "FAILED LOGIN ATTEMPTS DETECTED. SENDING AN EMAIL ALERT ..."
	FAILEDLOGINLOG=/tmp/failed_login_report_${ORACLE_SID}.log
	touch ${FAILEDLOGINLOG}
	# HTML Preparation:
	# #################
	case ${HTMLENABLE} in
	y|Y|yes|YES|Yes|ON|On|on)
	if [ -x /usr/sbin/sendmail ]
	then
	export SENDMAIL="/usr/sbin/sendmail -t"
	export MAILEXEC="echo #"
	export HASHHTML=""
	export HASHHTMLOS=""
	export ENDHASHHTMLOS=""
	export HASHNONHTML="--"
	SENDMAILARGS=$(
	echo "To: ${EMAIL};"
	echo "Subject: ${MSGSUBJECT} ;"
	echo "Content-Type: text/html;"
	echo "MIME-Version: 1.0;"
	cat ${FAILEDLOGINLOG}
	)
	export SENDMAILARGS
	else
	export SENDMAIL="echo #"
	export MAILEXEC="mail -s"
	export HASHHTML="--"
	export HASHHTMLOS="echo #"
	export ENDHASHHTMLOS=""
	export HASHNONHTML=""
	fi
	;;
	*)
	export SENDMAIL="echo #"
	export HASHHTML="--"
	export HASHHTMLOS="echo #"
	export ENDHASHHTMLOS=""
	export HASHNONHTML=""
	export MAILEXEC="mail -s"
	;;
	esac
	FAILEDLOGINOUTPUT=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 termout off echo off feedback off linesize ${SQLLINESIZE}
	EXEC DBMS_SESSION.set_identifier('${SCRIPT_NAME}');
	-- Enable HTML color format:
	${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { background: #E67E22; font-size: 80%; } th { background: #AF601A; } td { background: #E67E22; padding: 0px; } </style>' TABLE "border='1' bordercolor='#E67E22'" ENTMAP OFF
	SPOOL ${FAILEDLOGINLOG}
	prompt
	${HASHHTML} SET PAGES 0
	${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
	${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
	${HASHHTML} PROMPT FAILED LOGIN ATTEMPTS: [Last ${MINUTES} Minutes]
	${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
	${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { background: #E67E22; font-size: 80%; } th { background: #AF601A; } td { background: #E67E22; padding: 0px; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
	${HASHHTML} set pages 1000
	${HASHNONHTML} prompt ****************************************
	${HASHNONHTML} Prompt FAILED LOGIN ATTEMPTS [Last ${MINUTES} Minutes]
	${HASHNONHTML} prompt ****************************************
	set feedback off linesize ${SQLLINESIZE} pages 1000 timing on
	col TERMINAL FOR A30
	col ACTION_NAME FOR A20
	col TIMESTAMP FOR A21
	col OS_USERNAME FOR A20
	col DB_USERNAME FOR A20
	col DATE FOR A22
	col USERHOST FOR A30
	select to_char (ntimestamp#,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,USERID DB_USERNAME, spare1 OS_USERNAME, USERHOST, TERMINAL from aud\$
	Where ntimestamp# >= new_time(sysdate - ${MINUTES}/1440,'gmt','edt')
	and action# between 100 and 102
	and returncode = 1017
	${HASHDBUSERNAME} and lower (USERID) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (spare1) not in (${EXCLUDE_OSUSERS})
	order by ntimestamp#
	/
	PROMPT
	${NOINDEXWARNING}
	${NOINDEXWARNING1}
	${NOINDEXWARNING2}
	${NOINDEXWARNING3}
	/*
	select to_char (EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,OS_USERNAME,DB_USERNAME,TERMINAL,USERHOST,ACTION_NAME
	from DBA_AUDIT_SESSION
	where returncode = 1017
	and timestamp >= (sysdate - ${MINUTES}/1440)
	${HASHDBUSERNAME} and lower (USERNAME) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (OS_USERNAME) not in (${EXCLUDE_OSUSERS})
	order by EXTENDED_TIMESTAMP
	/
	*/
	SPOOL OFF
	exit;
	EOF
	)
	export MSGSUBJECT="ALERT: FAILED LOGIN ATTEMPT DETECTED ON [${ORACLE_SID}] ON [${SRV_NAME}]"
	echo ${MSGSUBJECT}
	SENDMAILARGS=$(
	echo "From: ${SENDER};"
	echo "To: ${EMAIL};"
	echo "Subject: ${MSGSUBJECT} ;"
	echo "Content-Type: text/html;"
	echo "MIME-Version: 1.0;"
	cat ${FAILEDLOGINLOG}
	)
	${MAILEXEC} "${MSGSUBJECT}" ${MAIL_LIST} < ${FAILEDLOGINLOG}
	echo ${SENDMAILARGS} | tr \; '\n' |awk 'length == 1 || NR == 1 {print $0} length && NR > 1 { print substr($0,2) }'| ${SENDMAIL}
	fi
	echo "FAILED LOGIN CHECK COMPLETED."
	echo
	;;
	esac
	# ####################
	# Check AUDIT RECORDS:
	# ####################
	case ${REPORT_AUDIT_RECORDS} in
	y|Y|yes|YES|Yes|ON|On|on)
	AUDITRECORDSCOUNTRAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	prompt
	-- Avoided using DBA_AUDIT_TRAIL view to not get the index on AUD\$(NTIMESTAMP#) ignored in the execution plan!
	select count(*) from aud\$ a, audit_actions act
	where a.action# = act.action (+)
	and a.ntimestamp# >= new_time(sysdate - ${MINUTES}/1440,'gmt','edt')
	${HASHDBUSERNAME} and lower (a.USERID) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (a.spare1) not in (${EXCLUDE_OSUSERS})
	${HASHACTIONNAME} and upper (act.NAME) not in (${EXCLUDE_ACTIONS})
	/
	/*
	select count(*) from DBA_AUDIT_TRAIL
	where timestamp >= (sysdate - ${MINUTES}/1440)
	${HASHDBUSERNAME} and lower (USERNAME) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (OS_USERNAME) not in (${EXCLUDE_OSUSERS})
	${HASHACTIONNAME} and upper (ACTION_NAME) not in (${EXCLUDE_ACTIONS})
	/
	*/
	exit;
	EOF
	)
	AUDITRECORDSCOUNT=`echo ${AUDITRECORDSCOUNTRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
	if [ ${AUDITRECORDSCOUNT} -ge ${RECORDSNUM} ]
	then
	echo "AUDIT RECORDS DETECTED. SENDING AN EMAIL ALERT ..."
	AUDITRECORDSLOG=/tmp/audit_records_report_${ORACLE_SID}.log
	touch ${AUDITRECORDSLOG}
	# HTML Preparation:
	# #################
	case ${HTMLENABLE} in
	y|Y|yes|YES|Yes|ON|On|on)
	if [ -x /usr/sbin/sendmail ]
	then
	export SENDMAIL="/usr/sbin/sendmail -t"
	export MAILEXEC="echo #"
	export HASHHTML=""
	export HASHHTMLOS=""
	export ENDHASHHTMLOS=""
	export HASHNONHTML="--"
	SENDMAILARGS=$(
	echo "To: ${EMAIL};"
	echo "Subject: ${MSGSUBJECT} ;"
	echo "Content-Type: text/html;"
	echo "MIME-Version: 1.0;"
	cat ${AUDITRECORDSLOG}
	)
	export SENDMAILARGS
	else
	export SENDMAIL="echo #"
	export MAILEXEC="mail -s"
	export HASHHTML="--"
	export HASHHTMLOS="echo #"
	export ENDHASHHTMLOS=""
	export HASHNONHTML=""
	fi
	;;
	*)
	export SENDMAIL="echo #"
	export HASHHTML="--"
	export HASHHTMLOS="echo #"
	export ENDHASHHTMLOS=""
	export HASHNONHTML=""
	export MAILEXEC="mail -s"
	;;
	esac
	AUDITRECORDSOUTPUT=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 termout off echo off feedback off linesize ${SQLLINESIZE}
	col name for A40
	EXEC DBMS_SESSION.set_identifier('${SCRIPT_NAME}');
	-- Enable HTML color format:
	${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { background: #E67E22; font-size: 80%; } th { background: #AF601A; } td { background: #E67E22; padding: 0px; } </style>' TABLE "border='1' bordercolor='#E67E22'" ENTMAP OFF
	SPOOL ${AUDITRECORDSLOG}
	prompt
	${HASHHTML} SET PAGES 0
	${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
	${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
	${HASHHTML} PROMPT Audit Records [Last ${MINUTES} Minutes]
	${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
	${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { background: #E67E22; font-size: 80%; } th { background: #AF601A; } td { background: #E67E22; padding: 0px; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
	${HASHHTML} set pages 1000
	${HASHNONHTML} prompt **********************************
	${HASHNONHTML} Prompt Audit Records [Last ${MINUTES} Minutes]
	${HASHNONHTML} prompt **********************************
	set feedback off linesize ${SQLLINESIZE} pages 1000 timing on
	col OS_USERNAME FOR A15
	col DB_USERNAME FOR A15
	col DATE FOR A22
	col OWNER FOR A15
	col OBJ_NAME FOR A25
	col USERHOST FOR A21
	col ACTION_NAME FOR A20
	col ACTION_OWNER_OBJECT FOR A80
	col SQL_TEXT FOR A100
	-- Avoided using DBA_AUDIT_TRAIL view to not get the index on AUD\$(NTIMESTAMP#) ignored in the execution plan!
	select to_char(a.NTIMESTAMP#,'DD-Mon-YYYY HH24:MI:SS')"DATE",a.spare1 OS_USERNAME, a.USERID DB_USERNAME, a.USERHOST, act.NAME||' '||a.OBJ\$CREATOR||' . '||a.OBJ\$NAME ACTION_OWNER_OBJECT, a.RETURNCODE, a.SQLTEXT
	from aud\$ a, audit_actions act
	where a.action# = act.action (+)
	and a.ntimestamp# >= new_time(sysdate - ${MINUTES}/1440,'gmt','edt')
	${HASHDBUSERNAME} and lower (a.USERID) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (a.spare1) not in (${EXCLUDE_OSUSERS})
	${HASHACTIONNAME} and upper (act.NAME) not in (${EXCLUDE_ACTIONS})
	order by a.ntimestamp#
	/
	PROMPT
	${NOINDEXWARNING}
	${NOINDEXWARNING1}
	${NOINDEXWARNING2}
	${NOINDEXWARNING3}
	/*
	select to_char(extended_timestamp,'DD-Mon-YYYY HH24:MI:SS')"DATE",OS_USERNAME,USERNAME DB_USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT,SQL_TEXT
	from dba_audit_trail
	where timestamp >= (sysdate - ${MINUTES}/1440)
	${HASHDBUSERNAME} and lower (USERNAME) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (OS_USERNAME) not in (${EXCLUDE_OSUSERS})
	${HASHACTIONNAME} and upper (ACTION_NAME) not in (${EXCLUDE_ACTIONS})
	order by EXTENDED_TIMESTAMP
	/
	*/
	SPOOL OFF
	exit;
	EOF
	)
	export MSGSUBJECT="ALERT: AUDIT RECORDS DETECTED ON [${ORACLE_SID}] ON [${SRV_NAME}]"
	echo ${MSGSUBJECT}
	SENDMAILARGS=$(
	echo "To: ${EMAIL};"
	echo "Subject: ${MSGSUBJECT} ;"
	echo "Content-Type: text/html;"
	echo "MIME-Version: 1.0;"
	cat ${AUDITRECORDSLOG}
	)
	${MAILEXEC} "${MSGSUBJECT}" ${MAIL_LIST} < ${AUDITRECORDSLOG}
	echo ${SENDMAILARGS} | tr \; '\n' |awk 'length == 1 || NR == 1 {print $0} length && NR > 1 { print substr($0,2) }'| ${SENDMAIL}
	fi
	echo "AUDIT RECORDS CHECK COMPLETED."
	echo
	;;
	esac
	done
	# #############
	# END OF SCRIPT
	# #############
	# REPORT BUGS to: mahmmoudadel@hotmail.com
	# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM: http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
	# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

view raw monitor_audit_records.sh hosted with ❤ by GitHub