#!/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>' >> ${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
# 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>' >> ${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:
Post a Comment