I need immediate help with this code. I do want to through the output of query into the log file and then on behelf of result i do want to automate an email warning system. while compiling this code i failed to through the output into the log file.
To avoid manual password change for our users we need to automate process of password expiry. We have to inform and ask user to change the password before the password get expire.
Action Points:
� Apply logic in Linux script based on output of expiry query to fetch username and\ no. Of days to expire. Follow suggestion to start with.
Mail will be send to each user whose password will expire in 15 days with Message 'Please change your password from web application as it will expire in x no. of days'.
#!/usr/bin/ksh
. ${HOME}/.profile
. /data01/dsproject/vdw/include/emailscript.ksh
INC001_SET_ENV admin
SCRIPTNAME=Email.ksh
TOOL=bteq
LOGFILE=${LOGDIR}/${SCRIPTNAME%.*}.log
echo "${DATE} ${TIME} START - \"${SCRIPTNAME}\"\n" > ${LOGFILE}
${TOOL} <<- EOF >> ${LOGFILE} 2>&1
.RUN FILE=${BASEDIR}/.dbadmin
-- Show temp tables from DBC.Tables
-Temp Table
SELECT
a.DatabaseName,
((100 * ((4 * NULLIFZERO(a.PasswordChgDate) - 1) / 146097)
+ (4 * (((4 * a.PasswordChgDate - 1) MOD 146097) / 4)
+ 3) / 1461 - 1900) + ((5 * (((4 * (((4 * a.PasswordChgDate
- 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
/ 12) * 10000 + (((5 * (((4 * (((4 * a.PasswordChgDate - 1)
MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
+ 1) * 100 + ((5 * (((4 * (((4 * a.PasswordChgDate - 1) MOD
146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
(DATE, FORMAT 'yy/mm/dd', NAMED PasswdChgDate),
b.expirepassword ,
Passwdchgdate + COALESCE(b.expirepassword,90) AS "NextChgDate",
(NextChgDate-DATE) AS NUM_of_Days_Left,
COALESCE(a.Profilename,'NOT ASSIGNED') AS Profilename
FROM dbc.DBASE a LEFT JOIN dbc.profileinfo b ON a.profilename = b.profilename
WHERE COALESCE(a.commentstring,'NOCMT') <> 'Not Used'
AND a.Passwordchgdate >= 0
AND NextChgDate BETWEEN DATE AN
.IF ERRORCODE != 0 THEN .EXIT ERRORCODE
.LOGOFF
.EXIT 0
EOF
i= ${LOGFILE}| wc -l
For i <> 0
User = awk '{print $1}' ${LOGFILE} | grep �n $i
Expiredays = awk '{print $2}' ${LOGFILE} | grep �n $i
Mailaddress = ${User}�@td.com�
i--
RC=$?
if [ "${RC}" != "0" ] ; then
echo "Error: ${SCRIPTNAME} exited with RC=${RC}" > ${MAIL_HEADER}
for I <> 0
mail -s ${SCRIPTNAME} -a ${LOGFILE} ${Mailaddress} < �Dear �${user �your password is about to expire in � ${ExpiredDays} �days�
exit 1
else
exit 0
fi
echo "\n" >> ${LOGFILE}
echo "${DATE} ${TIME} STOP - \"${SCRIPTNAME}"\" >> ${LOGFILE}
column username, format a20 head 'User'
column Now format a20 head "Now"
column Expire_Date format a20 head "Expire Date"
column Days format 9999 head "Days"
column Hrs format 99 head "Hrs"
column Min format 99 head "Mins"
column Sec format 99 head "Secs"
SELECT username, TO_CHAR(sysdate,'YYYY.MM.DD-HH24:MI:SS') "Now",
TO_CHAR(expiry_date,'YYYY.MM.DD-HH24:MI:SS') "Expire_Date",
trunc((((86400*(expiry_date-sysdate))/60)/60)/24) "Days",
trunc(((86400*(expiry_date-sysdate))/60)/60)-24*(trunc((((86400*(expiry_date-sysdate))/60)/60)/24)) "Hrs",
trunc((86400*(expiry_date-sysdate))/60)-60*(trunc(((86400*(expiry_date-sysdate))/60)/60)) "Min",
trunc(86400*(expiry_date-sysdate))-60*(trunc((86400*(expiry_date-sysdate))/60)) "Sec"
FROM dba_users where expiry_date is not null and expiry_date > sysdate;
Sample output:
User Now Expire Date Days Hrs Mins Secs
------- -------------------- -------------------- ----- --- ---- ----
XXX 2010.12.16-14:57:47 2011.03.14-12:54:22 87 21 56 35
YYY 2010.12.16-14:57:47 2011.02.03-16:42:15 49 1 44 28
Set heading off and spool to a file.
Than you can read the file, get the number of Days until expiration
test if it is less than or equal to your threshold and than send mail.
exec 9<full path of results from oracle
while read -u9 dataline
do
expire_days=$(echo $dataline | awk -F' ' ' { print $4 } ')
if [ $expire_days -le $threshold ]
then
user=$(echo $dataline | awk -F' ' ' { print $1 } ')
mailx command with information
fi
done
exec 9<&-
To do this in the shell would be a pain as that is why you probbably
posted your question
If it's Oracle, you can also create a stored proc using UTL_SMTP and send the emails from within Oracle. If you're interested, you can google it and find many examples of how to set it up.