Logging syslog messages to mysql using syslog-ng

The guide was built using CentOS 6.5, MySQL 5.6 Community Edition and Syslog-Ng. This guide assumes you already have MySQL up and running.

STEP 1. First we must download and install Extra Packages for Enterprise Linux (epel) repository for CentOS 6.

wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
sudo rpm -Uvh epel-release-6*.rpm

STEP 2. Install the following packages required for syslog-ng and mysql log destination.

yum install syslog-ng
yum install syslog-ng-libdbi
yum install libdbi-drivers
yum install libdbi-devel
yum install libdbi-dbd-mysql
yum install syslog-ng-libdbi

STEP 3. Create the following table in the syslog database in MySQL.

Create Table: 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,
  `datetime` datetime DEFAULT NULL,
  `program` varchar(15) DEFAULT NULL,
  `msg` text,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`seq`),
  KEY `date_host` (`datetime`,`host`)
) ENGINE=InnoDB AUTO_INCREMENT=14120 DEFAULT CHARSET=utf8

STEP 4. Create a MySQL user which has DELETE, INSERT, SELECT, UPDATE on the syslog database / schema.
Create user and set password
MySQL user1
Give the user “syslog” access to write to the database.
MySQL user2

STEP 5. Begin editing the syslog-ng.conf file

vim /etc/syslog-ng/syslog-ng.conf

Sample syslog-ng configuration file to save logs to a MySQL destination.

options {
flush_lines (0);
time_reopen (10);
log_fifo_size (1000);
long_hostnames (off);
use_dns (yes);
use_fqdn (yes);
create_dirs (yes);
keep_hostname (yes);
};

# syslog-ng log source
source s_net { udp(ip("172.16.1.20") port(514)); };
perm(0644) dir_perm(0700) create_dirs(yes)); };


source s_sys {
file ("/proc/kmsg" program_override("kernel: "));
unix-stream ("/dev/log");
internal();
};

destination d_file { file("/var/log/messagestest"); };
perm(0644) dir_perm(0700) create_dirs(yes)); };
destination d_cons { file("/dev/console"); };
destination d_mesg { file("/var/log/messages"); };
destination d_auth { file("/var/log/secure"); };
destination d_mail { file("/var/log/maillog" flush_lines(10)); };
destination d_spol { file("/var/log/spooler"); };
destination d_boot { file("/var/log/boot.log"); };
destination d_cron { file("/var/log/cron"); };
destination d_kern { file("/var/log/kern"); };
destination d_mlal { usertty("*"); };

# MySQL define destination
destination d_mysql {
sql(
type(mysql)
username("syslog")
password("Pass123!")
database("syslog")
host("172.16.1.20")
table("logs")
columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")
values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
indexes("datetime", "host")
);
};


filter f_kernel { facility(kern); };
filter f_default { level(info..emerg) and
not (facility(mail)
or facility(authpriv)
or facility(cron)); };
filter f_auth { facility(authpriv); };
filter f_mail { facility(mail); };
filter f_emergency { level(emerg); };
filter f_news { facility(uucp) or
(facility(news)
and level(crit..emerg)); };
filter f_boot { facility(local7); };
filter f_cron { facility(cron); };

# MySQL log to destination
log {source(s_net); destination(d_file); destination(d_mysql);};

# map source to destination fields.
log { source(s_sys); filter(f_kernel); destination(d_kern); };
log { source(s_sys); filter(f_default); destination(d_mesg); };
log { source(s_sys); filter(f_auth); destination(d_auth); };
log { source(s_sys); filter(f_mail); destination(d_mail); };
log { source(s_sys); filter(f_emergency); destination(d_mlal); };
log { source(s_sys); filter(f_news); destination(d_spol); };
log { source(s_sys); filter(f_boot); destination(d_boot); };
log { source(s_sys); filter(f_cron); destination(d_cron); };

STEP 6. Restart syslog-ng

service syslog-ng start

STEP 7. Log into MySQL and verify that logs are being saved to MySQL.

SELECT * 
FROM syslog.logs
order by datetime desc;

After completing these steps you should see the logs being saved to MySQL.
mysql-syslog-ng

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s