[转帖]mail-db

该程序用于分析sendmail的日志文件/var/log/maillog, 析出每条收信记录
的时间,收信人,来源,大小等数据, 并保存到PostgreSQL数据库中.
以便向用户收费
作者: callmedear.bbs@smth.org
---------------------------------------------------------------------------
#!/usr/bin/perl
#
# This perl script was written by callmedear.bbs@smth.org
# to analysis /var/log/maillog and insert records into the mail_db database.
#
use Pg;

$host="localhost";
$port="12345";
$dbname="mail_db";
$user="yourID";

# get the present year value
# enhanced for Y2K :-)
$TimeNow = time;
@TimeNow = gmtime( $TimeNow );
$YEAR = 1900 + $TimeNow[5];

%months =(  '01', 'Jan',
         '02', 'Feb',
         '03', 'Mar',
         '04', 'Apr',
         '05', 'May',
         '06', 'Jun',
         '07', 'Jul',
         '08', 'Aug',
         '09', 'Sep',
         '10', 'Oct',
         '11', 'Nov',
         '12', 'Dec' );
%dates  =(  '01', ' 1',
         '02', ' 2',
         '03', ' 3',
         '04', ' 4',
         '05', ' 5',
         '06', ' 6',
         '07', ' 7',
         '08', ' 8',
         '09', ' 9',
         '10', '10',
         '11', '11',
         '12', '12',
         '13', '13',
         '14', '14',
         '15', '15',
         '16', '16',
         '17', '17',
         '18', '18',
         '19', '19',
         '20', '20',
         '21', '21',
         '22', '22',
         '23', '23',
         '24', '24',
         '25', '25',
         '26', '26',
         '27', '27',
         '28', '28',
         '29', '29',
         '30', '30',
         '31', '31');

# connecting to database backend
$conn = Pg::connectdb("host=$host port=$port dbname=$dbname user=$user");
$status = $conn->status;
if( $status == PGRES_CONNECTION_OK )
{
# print "Connected to PostgreSQL server successfully!\n";
}
else
{
# print "Sorry, connect to database failed!

((\n";
exit 0;
}

# open the maillog file and store its contents in array @entry
open(MAILLOG,"/var/log/maillog") || ( $conn->reset && die );
@entry = <MAILLOG>;
close(MAILLOG);
chop( @entry );

# get the latest date & time in the database
Pg::doQuery($conn, "select max(time_receive)::date from log_tbl", \@last_date);
Pg::doQuery($conn, "select max(time_receive)::time from log_tbl", \@last_time);
@last_date_s = split(/-/, $last_date[0][0]);
$last_datetime = "$months{$last_date_s[0]} $dates{$last_date_s[1]} $last_time

[0;

# delete old records from the array @entry
if( grep(/^$last_datetime/, @entry) > 0 )
{
while( $entry[0] !~ /^$last_datetime/ )
{
      shift(@entry);
}
shift(@entry);
}

# get one "from" entry and find its "to" partner in the array @entry;
# get useful infomation and insert them into the database.
for($i=0; $i<=$#entry; $i++)
{
@field1 = split(/ +/, $entry[$i]);
if( $field1[6] =~ /^from=</ )
{
      for($j=$i+1; $j<=$#entry; $j++)
      {
         @field2 = split(/ +/, $entry[$j]);
         if( $field2[5] eq $field1[5] && $field2[6] =~ /^to=<.*geo\.pku\.edu)
         {
#             print "$field2[6]\n";
#             print "$field1[$#field1]\n";
#             print "$field1[7]\n";
            $field2[6] =~ s/to=<(.*)@.*/$1/;
            $field2[6] =~ tr/A-Z/a-z/;
            $address_loaction = $#field1;
            if( $field1[$#field1] =~ /localhost/ )
            {
                  last;
            }
            elsif( $field1[$#field1] !~ /\[/ )
            {
                  $address_loaction = $#field1 - 3;
            }
            $field1[$address_loaction] =~ s/.*\[(.*)\].*/$1/;
            $field1[7] =~ s/size=(.*),/$1/;
#             print "$field2[6]\n";
#             print "$field1[$address_loaction]\n";
#             print "$field1[7]\n\n";
            $conn->exec("insert into log_tbl values('$field2[6]', '$field1

[;
            last;
         }
      }
}
}

# decide whether the mails are from home or foreign countries.
$conn->exec("update log_tbl set home = TRUE where log_tbl.from_host between fre;
$conn->exec("update log_tbl set home = FALSE where home isnull");

# close connection to the database.
$conn->reset;
-----------------------------------------------------------------------------

收藏分享评分

回复引用

订阅 TOP

返回列表