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

0
60

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

Leave a Reply