Thursday, 17 July 2014

Oracle User expiry monitoring shell script and alerting through mail

#!/bin/bash
# This script is to send mail for alerting User Password expiry.
# Assumption smtb confirgured,TNS entry of db in tnsname.ora,created DIR's

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:$PATH
export SCRIPT_DIR=/u01/app/oracle/util/scripts
export LOG_DIR=/u01/app/oracle/util/log
export TNS_ADMIN=/u01/app/oracle/util/scripts
CURR_DATE=`date '+%m/%d/%y_%H:%M'`

if [ -e ${LOG_DIR}/usr_expire_status.txt ]
  then
    `rm   ${LOG_DIR}/usr_expire_status.txt `
fi
if [ -e ${LOG_DIR}/usr_expire_output.log ]
  then
    `rm ${LOG_DIR}/usr_expire_output.log `
fi

html_head="Subject: User Password Expiry Report for the Month: ${CURR_DATE}
\nMIME-Version: 1.0\nContent-Type: text/html\nContent-Disposition: inline\n
<html>
<head>


<meta http-equiv=\"Content-Type\" content=text/html; charset=\"UTF-8\">
<meta name=\"generator\" content=\"SQL*Plus 11.2.0\">
<style type=\"text/css\">
body {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif;
color:Black; background:#f7f7e7;
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif;
color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
h1 {font:13pt Arial,Helvetica,Geneva,sans-serif;
color:#336699;text-decoration:underline;background-color:White;
border-bottom:1px solid #cccc99;
margin-top:0pt; margin-bottom:0pt;
padding:0px 0px 0px 0px;}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;
color:#336699; background-color:White;
margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif;
color:#663300; background:#ffffff;
margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
</style>
<title>
Report Generated On: ${CURR_DATE}
</title>
</head>"
html_tail='<br><a><br>
<br> </a></body></html>'
reported_var=0
found_error=0
touch ${LOG_DIR}/usr_expire_status.txt


for SID in DBSID1 DBSID2 DBSID3
do
echo $SID
sqlplus -s username/password@${SID} << EOF
set feed off term off trims on linesize 300 pages 300 echo off underline off heading on
set markup html on
spool ${LOG_DIR}/usr_expire_output.log
 select username,expiry_date from dba_users where EXPIRY_DATE between sysdate and (sysdate + 30);
spool off
exit
EOF

reported_var=`grep 'USERNAME' ${LOG_DIR}/usr_expire_output.log | wc -l`
if test  ${reported_var} -gt 0
then
  found_error=1
  echo "<br><h1>The Database Name: ${SID} - Username and Date of Expiry details </h1><br>" >> ${LOG_DIR}/usr_expire_status.txt
  cat ${LOG_DIR}/usr_expire_output.log |grep -v 'SQL>'|grep -v 'SQL&gt'  >> ${LOG_DIR}/usr_expire_status.txt
fi
done
#if [ -e ${LOG_DIR}/User_Passwordexpiry_REPORT.html ]
#  then
#    `rm   ${LOG_DIR}/User_Passwordexpiry_REPORT.html `
#fi


echo -e $html_head >  ${LOG_DIR}/User_Passwordexpiry_REPORT.html
cat  ${LOG_DIR}/usr_expire_status.txt >>  ${LOG_DIR}/User_Passwordexpiry_REPORT.html
echo $html_tail >>  ${LOG_DIR}/User_Passwordexpiry_REPORT.html
#rm  ${LOG_DIR}/usr_expire_output.log
#rm ${LOG_DIR}/usr_expire_status.txt
if test $found_error -gt 0
then
sendmail -v username@mailid.com < ${LOG_DIR}/User_Passwordexpiry_REPORT.html
fi


##############
Sample Output

The Database Name: DBSID1- Username and Date of Expiry details


USERNAME
EXPIRY_DATE
Username
04-AUG-14



No comments: