maillog-status.sh

#!/bin/bash
# 2005/12/23
# crontab in every day, to record one day ago of mail log(maillog.log.1) status into db

smtpserv="210.17.16.88"
remotedbhost="localhost"

maillogfile=/var/log/maillog.1
yesterday=`date --date="1 day ago" +%F`
ignore1='aletest@xxx.com.tw'
ignore2='promote@xxx.com.tw'
ignore3='wod_hk@xxx.com.tw'
ignore4='wod_jp@xxx.com.tw'
ignore5='wod_tw@xxx.com.tw'
ignore6='wod_ltl@xxx.com.tw'
ignore7='wod_yahoo@xxx.com.tw'

cat $maillogfile | grep 'status=' |
                   grep 'to=' |
                   awk '{print $3 "    " $6 $7 $10}' |
                   sed -e 's/:to=</    /' -e 's/>,/    /' -e 's/status=/    /' |
while read lines; do
        tim=`echo $lines | awk '{print $1}'`
        qid=`echo $lines | awk '{print $2}'`
        usermail=`echo $lines | awk '{print $3}'`
        stat=`echo $lines | awk '{print $4}'`
        if [ "$usermail" == "$ignore1" ] ||
           [ "$usermail" == "$ignore2" ] ||
           [ "$usermail" == "$ignore3" ] ||
           [ "$usermail" == "$ignore4" ] ||
           [ "$usermail" == "$ignore5" ] ||
           [ "$usermail" == "$ignore6" ] ||
           [ "$usermail" == "$ignore7" ]; then
                :
        else
                echo "INSERT INTO maillog (smtpserver,timestamp,que_id,usermail,status)
                      VALUES ('$smtpserv','$yesterday $tim','$qid','$usermail','$stat')" |
                psql maillist -U alemail -h $remotedbhost
        fi
done