maillog_reason.sh

#!/bin/bash
# 2006/11/09 fixed by cross@ssorc.tw
# PURPOSE: 為了簡化 /var/log/maillog的資訊，進而查看退信或者是擱置時的理由為何
# How TO:
#       1. let maillog every day to rotate
#       2. create database ( following Section2 )
#       3. command maillog_reason.sh
#       4. crontab
set -x

# local server
smtp_server="192.168.1.1"

# database server to record
db_host="192.168.1.100"

# yesterday's maillog file
maillog_file=/var/log/maillog.1

yesterday=`date –date="1 day ago" +%F`

maillog_status_file=/tmp/maillog-status.txt
maillog_reason_file=/tmp/maillog-reason.txt
maillog_paste_file=/tmp/maillog-paste.txt

cat $maillog_file | grep 'status=' |
                    grep 'to=' |
                    grep -v 'sent' |
                    awk '{print $3 " " $6 $7 $10}' |
                    sed -e 's/:to=</ /' -e 's/>,/ /' -e 's/status=/ /' > $maillog_status_file

cat $maillog_file | grep 'to=' |
                    grep 'status=' |
                    grep -v 'sent' |
                    cut -d'(' -f2 |
                    sed -e 's/^/(/' > $maillog_reason_file

paste $maillog_status_file $maillog_reason_file > $maillog_paste_file

cat $maillog_paste_file |
while read lines; do
        tim=`echo $lines | awk '{print $1}'`
        qid=`echo $lines | awk '{print $2}'`
        mailto=`echo $lines | awk '{print $3}'`
        stat=`echo $lines | awk '{print $4}'`
        reason=`echo $lines | cut -d'(' -f2`
        mailfrom=`cat $maillog_file | grep $qid | grep 'from=<' | awk '{print $7}' | sed -e 's/from=<//' -e 's/>,//'`
#       echo $tim
        echo $qid
        echo mailto $mailto
#       echo $stat
#       echo $reason
        echo mailfrom $mailfrom

        echo "INSERT INTO reason (smtp_server,timestamp,queue_id,mailfrom,mailto,status,reason)
                      VALUES ('$smtp_server','$yesterday $tim','$qid','$mailfrom','$mailto','$stat','$reason')" |
                         mysql -h $db_host -u mailloguser -pmailloguser maillog_reason
done

## section2 database
# create database maillog_reason to import bellow script
# ———————————————————- #
#       ROP TABLE IF EXISTS `reason`;
#       CREATE TABLE `reason` (
#        `sid` int(11) NOT NULL auto_increment,
#        `smtp_server` varchar(128) NOT NULL default '',
#        `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
#        `queue_id` varchar(20) NOT NULL default '',
#        `mailto` varchar(128) NOT NULL default '',
#        `mailfrom` varchar(128) NOT NULL default '',
#        `status` varchar(128) NOT NULL default '',
#        `reason` text NOT NULL,
#       PRIMARY KEY  (`sid`)
#       ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
#
#       LOCK TABLES `reason` WRITE;
#       UNLOCK TABLES;
# ———————————————————- #