This article goal is to setup a rsyslog server to store syslog messages into multiple mysql db tables (based on message source), and then access those messages via http browser.

Actually, a couple of months ago I wrote a similar post using ubuntu server 10.10, it worked up to the moment I updated the server.
A few days ago a user (Colin) left a comment about his struggle to make a similar config under CentOS. After that I decided to give it another go, this time using CentOS.

Currently I just need to get the syslog messages from two devices (a cisco router and an accesspoint), however you can easily find out how to adapt this config to as many as you please.

Table of Contents:

Before you Start
1. MySQL
1.1 Install and run MySQL
1.2. Setup Database and Tables
2. Rsyslog
2.1. Install Rsyslog
2.2. Config Rsyslog
2.3. Allow Syslog Messages trough IpTable
2.4. Test Rsyslog
3. LogAnalyzer
3.1. Install LogAnalyzer
3.2. Setup LogAnalyzer
4. Final Notes

Before you Start

Before you start reading here are some overall notes on the setup.

Rsyslog:
Mysql DB Name: rsylogdb (tables gw1, ap1)
Mysql username: rsyslog

LogAnalizer:
Mysql DB Name: loganalizerdb
Mysql username: loganalizer

Local Networks: 10.0.0.0/27, 10.0.0.1/27
GW1 IP:10.0.0.30
AP1 IP:10.0.1.29
Syslog Server: 10.0.2.19
Syslog Port: 514/tcp
Server OS: CentOS 5.6 (OpenVZ VM)

Ok, let’s start.

1. MySQL

1.1 Install and run MySQL

Install with:

yum install php-mysql mysql mysql-server

Secure and Run:

/sbin/chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start
/usr/bin/mysql_secure_installation

Note: Last line will enable you to setup the mysql root password (initial password is blank, so if asked for it just hit Enter). Also it will enable you to secure the mysql config.
Also further steps could be made to secure the mysql config (change default port, root remote login, config allowed hosts, etc) but as this is a local server no need for that.

1.2. Setup Database and Tables

During "rsyslog-mysql” install, providing that mysql exists, the database and tables will be created automatically, default database table name is "Syslog” with "SystemEvents” and "SystemEventsProperties” tables. However the point is to split the messages over different tables so I will descrive how to manually create them.
Rsyslog database table schema is stored in a file called "createDB.sql”, however if your following this article, at this point you haven’t yet installed "rsyslog-mysql”.
Attention: The best (safest) option is to install "rsyslog” and "rsyslog-mysql” (read 2.1. Install Rsyslog) then come back here and complete the mysql config.

Check table schema:

find -name createDB.sql
#outputs(in my case)
./usr/share/doc/rsyslog-mysql-3.22.1/createDB.sql
#edit file and copy schema
vi /usr/share/doc/rsyslog-mysql-3.22.1/createDB.sql

Create the user/database/table and table schema:

#log to mysql
mysql -u root -p

#create a user
CREATE USER rsyslog;
SET PASSWORD FOR rsyslog= PASSWORD("yourpasswordgoeshere");

#setup database and table schema
CREATE DATABASE rsyslogdb;
USE rsyslogdb;
#paste contents of createDB.sql (the following is for rsyslog-mysql-3.22.1)
CREATE TABLE SystemEvents
(
 ID int unsigned not null auto_increment primary key,
 CustomerID bigint,
 ReceivedAt datetime NULL,
 DeviceReportedTime datetime NULL,
 Facility smallint NULL,
 Priority smallint NULL,
 FromHost varchar(60) NULL,
 Message text,
 NTSeverity int NULL,
 Importance int NULL,
 EventSource varchar(60),
 EventUser varchar(60) NULL,
 EventCategory int NULL,
 EventID int NULL,
 EventBinaryData text NULL,
 MaxAvailable int NULL,
 CurrUsage int NULL,
 MinUsage int NULL,
 MaxUsage int NULL,
 InfoUnitID int NULL ,
 SysLogTag varchar(60),
 EventLogType varchar(60),
 GenericFileName VarChar(60),
 SystemID int NULL
);
CREATE TABLE SystemEventsProperties
(
 ID int unsigned not null auto_increment primary key,
 SystemEventID int NULL ,
 ParamName varchar(255) NULL ,
 ParamValue text NULL
);
#rename SystemEvents
rename table SystemEvents to gw1;

#duplicate table
CREATE TABLE ap1 LIKE rsyslogdb.gw1;

#grant rsyslog user privileges over database
GRANT ALL PRIVILEGES ON rsyslogdb.* TO rsyslog IDENTIFIED BY "yourpasswordgoeshere";
flush privileges;

#leave mysql
exit

^ Back to Top ^

2. Rsyslog

2.1. Install Rsyslog

First remove sysklogd (default centos syslog daemon)

yum remove sysklogd

Install Rsyslog with mysql support

yum install rsyslog rsyslog-mysql

Note: If your running mysql inside an openvz vm (like me), when using "yum” to install anything, you may get an "thread.error” . This is a known bug with "fastestmirror” feature of "yum” and "mysql”.
To bypass this problem, either disable "fastestmirror” or stop mysql:

yum --disableplugin=fastestmirror install rsyslog rsyslog-mysql
#or
yum --noplugins install rsyslog (I prefer this one)
#or
service mysqld stop
#install whatever you want, then:
service mysqld start

2.2. Config Rsyslog

Now, lets config rsyslog:

vi /etc/rsyslog.conf
#Add (note that I only use TCP port 514, you can use UDP and any other port):
$ModLoad ommysql
$ModLoad imtcp
$InputTCPServerRun 514

#define the allowed senders (either by host or network, I prefer the second one):
$AllowedSender TCP, 127.0.0.1, 10.0.0.0/27, 10.0.1.0/27
$AllowedSender TCP, 127.0.0.1, 10.0.0.30, 10.0.1.29

#create custom templates and source rules:
$template gw1tmpl,"insert into gw1 (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL
$template ap1tmpl,"insert into ap1 (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL
if ($source == '10.0.0.30') then :ommysql:127.0.0.1,rsyslogdb,rsyslog,passwordgoeshere;gw1tmpl
if ($source == '10.0.1.29') then :ommysql:127.0.0.1,rsyslogdb,rsyslog,passwordgoeshere;ap1tmpl

In case of trouble, here is my rsyslog.conf file.

It may prove useful to setup an "aggregation table”, that will log all messages (to check all messages at once):

#Create "SystemEvents" table :
(in mysql)
use rsyslogdb;
CREATE TABLE SystemEvents LIKE rsyslogdb.gw1;
exit
(edit rsyslog.conf)
vi /etc/rsyslog.conf
(add this line before the rule/templates [if($souce)...])
*.* >127.0.0.1,rsyslogdb,rsyslog,passwordgoeshere

Note that "rsyslog.conf” file is sequential, so if you place the "aggregation table” before the "if statements”, when a syslog message arrives, it will be stored first in the "aggregation table”, then in the table pointed by the corresponding statement (if any).

At last, write rsyslog.conf, exit, and restart the service:

service rsyslog restart

2.3. Allow Syslog Messages trought IpTables

If you run a firewall (iptables), you will need to open a port, like this:

iptables -I INPUT -p tcp -i eth0 -s 10.0.0.30 -d 10.0.2.19 --dport 514 -j ACCEPT
iptables -I INPUT -p tcp -i eth0 -s 10.0.1.29 -d 10.0.2.19 --dport 514 -j ACCEPT
#adapt protocol/port to your config

2.4. Test Rsyslog

Let’s do some testing.
Check if messages are arriving at the syslog server.

tail -f /var/log/messages

Check if messages are being stored in mysql database.

mysql -u root -p
use rsyslogdb;
select * from gw1;

If you see anything else than "empty set” it’s working

^ Back to Top ^

3. LogAnalyzer

3.1. Install LogAnalyzer

First, you will need to install apache, php and the mysql connector

yum install httpd php php-mysql
#remember "service mysqld stop/start" if you run openvz and get "thread.error"
chkconfig --levels 235 httpd on
service httpd start

Check for the last stable release, download and install

cd /tmp
wget http://download.adiscon.com/loganalyzer/loganalyzer-3.2.1.tar.gz
#untar
tar -xvzf loganalyzer-3.2.1.tar.gz
#cd to src directory
cd loganalyzer-3.2.1/src
#clear /var/www/html (remove apache default index)
rm -R -f /var/www/html
#Copy the content to your the webserver root (/var/www)
cp -R * /var/www/html
#and repeat for the contrib folder:
cd /tmp/loganalyzer-3.2.1/contrib/
cp * /var/www/html
#go to webroot and give execute scripts
cd /var/www/html
chmod +x configure.sh secure.sh
./configure.sh

Note:The last line will create a blank "config.php” file, and will give everyone write access to it.
It won´t generate any output, so don’t panic, just do a "ls” to check if the config.php file has been created (initial setup via browser will make changes to this file).

3.2. Setup LogAnalyzer

Setup LogAnalizer MySQL user and database:

#log into mysl
mysql -u root -p
create database loganalyzerdb;
CREATE USER loganalyzer;
SET PASSWORD FOR loganalyzer= PASSWORD("yourpasswordgoeshere");
GRANT ALL PRIVILEGES ON loganalyzerdb.* TO loganalyzer IDENTIFIED BY "yourpasswordgoeshere";
flush privileges;
exit

Now point your browser to the server ip.

You will be presented with the following message, proceed ("Click here…”)

Click proceed until you reach this page, then setup loganalyzer viewing preferences.

Setup the database as seen above.

Now somewhere along this process you will be asked if you want to setup a loganalyzer database to store users. Insert the database name and user you have created before.

After the setup, you will need to add another (one or several) log sources (aka database tables). Go to Admin Center > Sources and click on "Add new source”, and insert the same config that before, only changing the database table.

After I had setup my sources I logged out of loganalyzer and I was still was able to access those sources, so I had to edit the sources again (via browser) and click on the checkbox "user only” to make them private to the user how created them (you can see in the image above that they where assigned "Global”). Then we need to edit loganalyzer config file:

vi /var/www/html/config.php
#change line
$CFG['UserDBLoginRequired'] = false;
#to
$CFG['UserDBLoginRequired'] = true;

Finally, All done.
^ Back to Top ^

4. Final Notes:

As I said, I run a cisco router and ap, so here’s how to activate syslog on those devices:

logging host 10.0.2.19 transport tcp port 514 audit
logging trap debugging

And that’s it, your done. Now relax, SUMMER IS HERE.

Related posts: