pf-savelog-mysql.pl
-------------------

This is a Perl script, which you can use for parsing log entries from
postfix (http://postfix.org) MTA and store into MySQL database. It is
designed for syslog-ng, because syslog-ng is capable log directly to
pipe. But you can run this script from cron etc.



INSTALLATION
------------


1. Download
-----------

Download pf-savelog-mysql.pl and also required Perl modules. The best
way is to get fresh CVS version from cvs.platon.sk server, or download
from
http://platon.sk/cvs/cvs.php/scripts/perl/postfix/ and
http://platon.sk/cvs/cvs.php/perl-modules/

# cvs -d :pserver:anonymous@cvs.platon.sk:/home/cvs login
# cvs -d :pserver:anonymous@cvs.platon.sk:/home/cvs co perl-modules
# cvs -d :pserver:anonymous@cvs.platon.sk:/home/cvs co scripts/perl/postfix

Put perl-modules directory somewhere, where it will be found by
pf-savelog-mysql.pl script. Directory structure like this should be OK:

# Alternative 1:
/somewhere/
/somewhere/perl-modules/
/somewhere/perl-modules/Platon/
/somewhere/perl-modules/Platon/Log/
/somewhere/perl-modules/Platon/Log/Mail/
/somewhere/perl-modules/Platon/Log/Mail/Postfix.pm
/somewhere/scripts/
/somewhere/scripts/perl/
/somewhere/scripts/perl/postfix/
/somewhere/scripts/perl/postfix/pf-savelog-mysql.pl

# Alternative 2:
/somewhere/
/somewhere/scripts/
/somewhere/scripts/perl/
/somewhere/scripts/perl/postfix/
/somewhere/scripts/perl/postfix/pf-savelog-mysql.pl
/somewhere/scripts/perl/postfix/perl-modules/
/somewhere/scripts/perl/postfix/perl-modules/Platon/
/somewhere/scripts/perl/postfix/perl-modules/Platon/Log/
/somewhere/scripts/perl/postfix/perl-modules/Platon/Log/Mail/
/somewhere/scripts/perl/postfix/perl-modules/Platon/Log/Mail/Postfix.pm


2. Configure database connection
--------------------------------

Now create and configure your connection to database. Save syslog-ng.cnf
example configuration as /etc/syslog-ng/mysql.conf and set your login
and password for database.


3. Create database tables
-------------------------

# cat db-structure.sql | mysql -u username -h database.company.com -p

4. Configure syslog-ng
----------------------

Add following lines to your /etc/syslog-ng/syslog-ng.conf config file.
If you already have syslog-ng configured, you should add only lines
with "dp_mail" keyword:

#-----------------------------------%<-----------------------------------
# all known message sources
source s_all {
        # message generated by Syslog-NG
        internal();
        # standard Linux log source (this is the default place for the syslog()
        # function to send logs to)
        unix-stream("/dev/log");
        # messages from the kernel
        file("/proc/kmsg" log_prefix("kernel: "));
        # use the above line if you want to receive remote UDP logging messages
        # (this is equivalent to the "-r" syslogd flag)
        # udp();
};

# pf-savelog-mysql.pl configuration
destination df_mail { file("/var/log/mail/$YEAR-$MONTH-$DAY-mail.log"               template("$ISODATE $HOST [$LEVEL] $MSG\n")); };
destination dp_mail { program("/somewhere/scripts/perl/postfix/pf-savelog-mysql.pl" template("$ISODATE $HOST [$LEVEL] $MSG\n")); };
filter f_mail { facility(mail); };

log { 
    source(s_all);
    filter(f_mail);
    destination(df_mail);
    destination(dp_mail);
};
#-----------------------------------%<-----------------------------------


5. Restart syslog-ng daemon
---------------------------

You should restart your syslog-ng daemon with command:

# /etc/init.d/syslog-ng restart


6. DONE!
--------

I hope, your configuration now works fine.


7. Some notes
-------------

You can import your old postfix logfiles with command:

# /somewhere/scripts/perl/postfix/pf-savelog-mysql.pl < /var/log/mail/mail.log

--
Document written by Lubomir Host 'rajo' <rajo AT platon.sk>
Fri, 16 Sep 2005 22:51:50 +0200