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='[email protected]'
ignore2='[email protected]'
ignore3='[email protected]'
ignore4='[email protected]'
ignore5='[email protected]'
ignore6='[email protected]'
ignore7='[email protected]'

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