[syslog-ng]logging into database

=?iso-8859-1?q?hicham=20rahimi?= rahimihicham@yahoo.fr
Fri, 16 Aug 2002 13:49:24 +0200 (CEST)


 i want to setup logging syslog-ng'messages into a
mysql database ,in order to make the messages
organization more powerful.
  i have tested an exemple given in www.vermeer.com
but it doesn't work.
  if any one have tested it , tell me please if it
contain a bug or not
     here  the exemple:
Centralized syslog-ng to mysql database

by Michael Earls 

To established a centralized location for syslog-ng
collection in order to facilitate: 

Immediate log reporting (both real time and summary
Long term log storage (for archival and for possible
later analysis). 
Tools used: 
syslog-ng  -- template() destinations are only
supported by the 1.5.x releases. 
My Own PhP Files 
Syslog-ng/Pipe Scripts 
MySql Replication 

Demo https://daffy.chmcc.org/syslog//
     This site is still in testing phase

Demo-files The files I used for the Demo -- NEW DEMO
FILE -- 

To setup syslog-ng to log to a mysql database.  This
assumes that you have installed and setup syslog-ng
and mysql.

Edit the syslog-ng.conf file
       This tells syslog-ng to pipe to a fifo template

Add the following lines --

                                    ## Log syslog-ng
to mysql database 
d_mysql {
template("INSERT INTO logs (host, facility, priority,
level, tag, date, 
program, msg) VALUES ( '$HOST', '$FACILITY',
'$MSG' );\n") template-escape(yes));
                                        log {
source(net); destination(d_mysql);

Then comment out this line --

                                        # This is the
default behavior of sysklogd package
                                        # Logs may
come from unix stream, but not from another machine.
                                        #source src {
unix-dgram("/dev/log"); internal(); };

Then uncomment out this line --

                                        # If you wish
to get logs from remote machine you should uncomment
                                        # this and
comment the above source line.
                                        source src {
unix-dgram("/etc/log/log"); internal(); };
                                        source net {
udp(); };


Create the database for syslog-ng --
       Create a file called syslog.sql and paste this
below, this will create the database " syslog" and
table " logs" in mysql.
                        # Table structure for table

                        CREATE DATABASE syslog;

                        USE syslog;

                        CREATE TABLE logs (
                        host varchar(32) default NULL,
                        facility varchar(10) default
                        priority varchar(10) default
                        level varchar(10) default
                        tag varchar(10) default NULL,
                        date date default NULL,
                        time time default NULL,
                        program varchar(15) default
                        msg text,
                        seq int(10) unsigned NOT NULL
                        PRIMARY KEY (seq),
                        KEY host (host),
                        KEY seq (seq),
                        KEY program (program),
                        KEY time (time),
                        KEY date (date),
                        KEY priority (priority),
                        KEY facility (facility)
                        ) TYPE=MyISAM;

        Run this command
                    mysql -u root -p < syslog.sql

This will install the database into mysql.

Create a fifo pipe file -- 
          This is the file that syslog-ng will store
records before writing to the database.               

                    mkfifo /tmp/mysql.pipe

You need to restart syslog-ng --
            /etc/init.d/syslog-ng stop   # Stop
            /etc/ini.d/syslog-ng start    # Start

Run this command to pipe the file mysql.pipe to mysql

You need to create a script that will check to make
sure this command is running and restart if stopped.
Syslog-ng/Pipe scripts

         When this file is started it will hang, You
need  to create a script and have it run on startup.

                 mysql -u root --password=passwd
syslog < /tmp/mysql.pipe



This is a mini how-to and is in the second
draft::Thanks again to everyone..


Any questions just email me 

Created Jan 18, 2002
Last Updated May 18, 2002 

Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en franšais !
Yahoo! Mail : http://fr.mail.yahoo.com