统计一台服务器所有邮局发送和接收情况

#!/bin/sh
####################################################################################################
GAWK=/usr/local/bin/gawk
FIND=/usr/bin/find
GREP=/usr/bin/grep
TAI64NLOCAL=/usr/local/bin/tai64nlocal

ROOT_PATH=/tmp/KevinShell/statistic
LOG_TMP_PATH=${ROOT_PATH}/tmp
OUT_PUT_PATH=${ROOT_PATH}/output
HEAD_PATH=${ROOT_PATH}/head

mkdir -p $LOG_TMP_PATH
mkdir -p $OUT_PUT_PATH

TODAY=`date +%Y-%m-%d`
MONTH=`date +%Y%m`

HOST=`hostname`


SQL=`/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e`
head=${HEAD_PATH}/statistic_send.head
tmp_log=${LOG_TMP_PATH}/shm_send.log
output_log=${OUT_PUT_PATH}/statistic_send.log.$MONTH

/usr/local/bin/shm_read -a | $GREP ^DOMAIN > $tmp_log

#统计一台服务器上所有邮局的总发送量TOTAL，外发量OUTSIDE，本地发送量INSIDE

count=`$GAWK -F"\t" '{print $5}' $tmp_log | $GAWK -F":" '{count+=$2} END{print count}'`
count_diff=`$GAWK -F"\t" '{print $6}' $tmp_log | $GAWK -F":" '{count+=$2} END{print count}'`

#将上述统计数据插入到数据库中
/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value) VALUES ('$TODAY','$HOST','SEND_MAIL','TOTAL',$count)"
/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value) VALUES ('$TODAY','$HOST','SEND_MAIL','OUTSIDE',$count_diff)"
/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value) VALUES ('$TODAY', '$HOST', 'SEND_MAIL','INSIDE' ,`expr $count - $count_diff`)"

# 对邮件发送数据的前200名进行统计
output_log=${OUT_PUT_PATH}/statistic_send_pre200.log.$MONTH

$GAWK -F"\t" '{print $5, ":", $2}' $tmp_log | $GAWK -F":" '{print $2, $4}' | sort -nr | head -200 >> $output_log

rm $tmp_log
# 对邮件接收的数据进行统计
head=${HEAD_PATH}/statistic_recv.head
tmp_log=${LOG_TMP_PATH}/shm_recv.log
output_log=${OUT_PUT_PATH}/statistic_recv.log.$MONTH

$FIND /var/log/qmail/qmail-smtpd/ -type f -mtime -48h -exec cat {} \; | $TAI64NLOCAL | $GREP '$TODAY' > $tmp_log

recv_rejected=`$GREP "CHKUSER rejected" $tmp_log | wc -l`
recv_rejected_not_existing=`$GREP "not existing recipient" $tmp_log | wc -l`
recv_rejected_to_relay=`$GREP "client not allowed to relay" $tmp_log | wc -l`

recv_accepted=`$GREP "CHKUSER accepted" $tmp_log | wc -l`
recv_accepted_in_RBL=`$GREP "is listed in RBL" $tmp_log | wc -l`
recv_accepted_same_domain=`$GREP "sender and rcpt are in the same domain" $tmp_log | wc -l`
recv_accepted_mailmx=`$GREP ":mailmx[0-9]*.dns.com.cn:" $tmp_log | wc -l`
recv_accepted_matched=`$GREP 'matched!' $tmp_log | wc -l`

if [ ! -e $output_log ]
then
    cat $head >> $output_log
fi

printf "| %10s | %16d | %22d | %20d | %16d | %6d | %18d | %6d | %7d |\n" \
    '$TODAY' \
    $recv_rejected $recv_rejected_not_existing $recv_rejected_to_relay \
    $recv_accepted $recv_accepted_in_RBL $recv_accepted_same_domain $recv_accepted_mailmx $recv_accepted_matched >> $output_log



/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic(Date , Server , Item , SubItem , Value) VALUES ('$TODAY', '$HOST','RECV_MAIL','recv_rejected',$recv_rejected)"
/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value ) VALUES ('$TODAY','$HOST','RECV_MAIL','recv_rejected_not_existing',$recv_rejected_not_existing)"
/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value ) VALUES ('$TODAY','$HOST','RECV_MAIL','recv_rejected_to_relay',$recv_rejected_to_relay)"
/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value ) VALUES ('$TODAY','$HOST','RECV_MAIL','recv_accepted',$recv_accepted)"
/usr/local/mysql/bin/mysql -uroot -pPASSWDy -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value ) VALUES ('$TODAY','$HOST','RECV_MAIL','recv_accepted_in_RBL',$recv_accepted_in_RBL)"
/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value ) VALUES ('$TODAY', '$HOST', 'RECV_MAIL','recv_accepted_same_domain',$recv_accepted_same_domain)"
/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value ) VALUES ('$TODAY', '$HOST', 'RECV_MAIL','recv_accepted_mailmx',$recv_accepted_mailmx)"
/usr/local/mysql/bin/mysql -uroot -pPASSWD -hlocalhost -DStatistic -e"insert into Statistic ( Date , Server , Item , SubItem , Value ) VALUES ('$TODAY', '$HOST', 'RECV_MAIL','recv_accepted_matched',$recv_accepted_matched)"

rm $tmp_log

其实上述的整个实现机制是很简单的，就是从日志里面统计数据，然后将数据插入到提前建好的数据库对应的表中

最后表中的数据如下：

数据库以及表是这么建的

mysql> create database Statistic;
Query OK, 1 row affected (0.02 sec)

mysql> use Statistic;
Database changed

mysql> create table Statistic(
    -> Id int(11) primary key  auto_increment,
    ->  Date date,
    -> Server varchar(255),
    -> Item varchar(255),
    -> SubItem varchar(255),
    -> Value int(11),
    -> Addition varchar(255)
    -> )
    -> ;
Query OK, 0 rows affected (0.03 sec)

或者是

mysql> create table Statistic( Id int(11) primary key  auto_increment,  Date date, Server varchar(255), Item varchar(255), SubItem varchar(255), Value int(11), Addition varchar(255) );

mysql> desc Statistic;