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 ( port (514));

tcp (ip ( port (51400));

The 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

Setup the MySQL database like this:

USE syslog

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,
KEY host (host),
KEY seq (seq),
KEY program (program),
KEY time (time),
KEY date (date),
KEY priority (priority),
KEY facility (facility)

# Also create the user, replace username and password
ALL PRIVILEGES ON the GRANT syslog. * TO Syslogng @ localhost 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 Cisco syslog 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

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

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


Leave a Reply