[syslog-ng]logging into database

[=?iso-8859-1?q?hicham=20rahimi?=]

 hi,

 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
  thanks
         hicham; 
 
     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
reporting) 
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. 
mysql  
My Own PhP Files 
phpmyedit 
phpmyadmin 
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 
                                    ##                
                
                                        destination
d_mysql {
                                           
pipe("/tmp/mysql.pipe"
                                           
template("INSERT INTO logs (host, facility, priority,
level, tag, date, 
                                            time,
program, msg) VALUES ( '$HOST', '$FACILITY',
'$PRIORITY', '$LEVEL', '$TAG', 
                                           
'$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM',
'$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
`logs`
                        #

                        CREATE DATABASE syslog;

                        USE syslog;

                        CREATE TABLE logs (
                        host varchar(32) default NULL,
                        facility varchar(10) default
NULL,
                        priority varchar(10) default
NULL,
                        level varchar(10) default
NULL,
                        tag varchar(10) default NULL,
                        date date default NULL,
                        time time default NULL,
                        program varchar(15) default
NULL,
                        msg text,
                        seq int(10) unsigned NOT NULL
auto_increment,
                        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
syslog-ng
            /etc/ini.d/syslog-ng start    # Start
syslog-ng

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

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..

Michael

Any questions just email me 
--Michael@michaelearls.com--

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