Setting Up Remote Syslog to MySQL With Cisco IOS and Syslog-ng in Linux

First, syslog-ng

I use Ubuntu, so I can also use their practical package manager and run

apt-get install syslog-ng

Then whip up /etc/syslog-ng/syslog-ng.conf in your favourite editor and add this to the configuration.

source s_net {

udp(ip(10.0.0.58) port(514));

tcp(ip(10.0.0.58) port(51400));

};

The 10.0.0.58 should be the IP address that you want syslog-ng to listen on, it has to be bound up to the server that runs syslog-ng.

Also add this to make syslog-ng write to a special pipe:

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));

};

And to make things that comes from s_net go to d_mysql:

log {

source(s_net);

destination(d_mysql);

};

Make a pipe that syslog-ng can write to with this command:

mkfifo /tmp/mysql.pipe

MySQL

Setup the MySQL database like this:

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;

# Also create the user, replace username and password

GRANT ALL PRIVILEGES ON syslog.* TO [email protected] IDENTIFIED BY ‘mypassword’;

Run this command to pipe the queries to MySQL, preferably in a screen or make a script that can run it in the background.

mysql -u syslogng –password=mypassword syslog Cisco Syslog Configuration

Now all you have to do on the cisco router is one simple command to make it log to the syslog database.

Router(config)# logging 10.0.0.58

This will make the Cisco Router send all logging output to the syslog-ng process on 10.0.0.58

I have made a simple PHP page that makes the syslog output more viewable, it is something one can do with ease.



Source link