A Syslog Server using Rsyslog, MySQL and LogAnalyzer
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.
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
2. Rsyslog
2.1. Install Rsyslog
First remove sysklogd (default centos syslog daemon)yum remove sysklogd
Install Rsyslog with mysql supportyum 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
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
3. LogAnalyzer
3.1. Install LogAnalyzer
First, you will need to install apache, php and the mysql connectoryum 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 installcd /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.
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
Separate Rsyslog logging over multiple database tables
This post explains how to separate rsyslog log messages, over different mysql database tables. If you need to log from more than one device, and if you want to have separate logging to database, then this is the way to go. And it can be combined with the phplogcon (aka loganalyzer) multiple database source config.
I have written a series of post explaining how to setup rsyslog with mysql database and setting a webserver with phplogcon for accessing the log messages over my browser. you can check this posts using the following links:
In my home LAN I have 2 cisco devices I want to monitor, a cisco router and a cisco accesspoint, so this will be my quest, as for you guys i hope you can manage to adapt this information to your needs.
For the rest of this post I will assume that my router ip is 192.168.1.1, ap is 192.168.1.2, and rsyslog server is 192.168.1.3. I will also assume that you have read my other posts and that you have already a rsyslog+mysql+phplogcon config working.
My database table names will be “gateway” and “accesspoint”, and I will use the default rsyslog database name “Syslog”, with the default user “rsyslog”.
First login to mysql, select the Syslog database and rename the “SystemEvents” table:#log to mysql
mysql -u root -p
#select db
use Syslog;
#rename table
rename table SystemEvents to gateway;
Now let’s duplicate the “gateway” table it (do this for as many tables you need):# in this step I create a accesspoint table with same config as the gateway table
CREATE TABLE accesspoint LIKE Syslog.gateway;
Let’s give privileges for our database user over the new tables:
There are 2 ways to do this:#give privileges over a single new table
GRANT ALL PRIVILEGES ON Syslog.accesspoint TO rsyslog IDENTIFIED BY "passwordhere";
#or if you have many tables then probably the best is to go this way:
#list users
SELECT user, host, password FROM mysql.user;
#remove the rsyslog user:
DELETE FROM mysql.user WHERE User='rsyslog';
#then flush privileges: ATENTION: this is a "must do" after changing mysql users.
flush privileges;
#create user again
CREATE USER 'rsyslog'@'localhost' IDENTIFIED BY 'passwordhere';
#and now give privileges over every table inside the database
GRANT ALL PRIVILEGES ON Syslog.* TO rsyslog IDENTIFIED BY "passwordhere";
Ok now let´s go to the rsyslog config. Edit /etc/rsyslog.conf file, and add the following (omit the lines you already have):$ModLoad imtcp
$InputTCPServerRun 514
$ModLoad ommysql
$template gw1,"insert into gateway (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL
if ($source == '192.168.1.1') then :ommysql:127.0.0.1,Syslog,rsyslog,passwordhere;gw1
$template ap1,"insert into accesspoint (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL
if ($source == '192.168.1.2') then :ommysql:127.0.0.1,Syslog,rsyslog,passwordhere;ap1
Now reload the config, and just to be sure restart the service:sudo service rsyslog reload
sudo service rsyslog restart
Now we need to configure the clients, in my case, i will login to my cisco router and ap, and issue the following lines:configure terminal
logging host 192.168.1.3 transport tcp port 514 audit
#Atention: you should use tcp to deliver log messager instead of udp, to garrantee the messeges are received by the server.
Ok perhaps now its time to test the config. In my cisco devices I will give some debug command (some that can generate output), then login the mysql server:mysql -u root -p
#select db
use Syslog;
#list content of table
select * from gateway;
select * from accesspoint;
Ok i haven’t got the “Empty Set” message, so i´m cool.
Last Part! The Phplogcon config (phplogcon is now known as loganalyzer, but it is not stable yet, so no good for me).
All there is to do, is to login to phplogcon over my browser and edit my sources (“Admin Center”>”Sources”)
(check the images bellow)
And that all, here is my nice multisource phplogcon config (with some data omitted to protect the innocent)
Installing LogAnalyzer and rsyslog on CentOS
Instructions on how to set up Linux modules needed to get a LogAnalyzer log aggregation/analysis server up and running and collecting logs.
Prerequisites
These instructions are specific to CentOS 6.2. If you are using a different distro, many of the installation commands and paths to files will be different from what I've documented below. I strongly suggest that you document the steps to perform a similar install for your distro.
You will need to install the prerequisites by using the following commands:yum install httpd
yum install mysql
yum install mysql-server
yum install php
yum install php-mysql
yum install php-gd
yum install rsyslog
yum install rsyslog-mysql
/usr/bin/updatedb
The '/usr/bin/updatedb' command updates the file index so that the 'find' and 'locate' commands work properly. If you've already properly set up your system to index the files daily, this will be unnecessary.
If your distro of Linux is using a different syslog server such as syslog-ng or sysklogd, you'll need to remove it.
MySQL
Set up MySQL/sbin/chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start
/usr/bin/mysql_secure_installation
Hit enter key after last command has run since no password has yet been set for root MySQL account. Hit 'y' and enter when asked to set up a root password and type in a strong password. Hit 'y' and enter for the following questions: "Remove anonymous users?", "Disallow root login remotely?", "Remove test database and access to it?", and "Reload privilege tables now?"
Set up database and tables
Create the user/database/table and table schema:
Log in to mysql:mysql -u root -p
Create a user:CREATE USER rsyslog;
SET PASSWORD FOR rsyslog= PASSWORD('yourpasswordgoeshere');
Set up database and table schema:CREATE DATABASE rsyslogdb;
USE rsyslogdb;
Paste contents below to mysql to set up the schema: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
);
Next, we need to grant permissions to the rsyslog account we created earlier:GRANT ALL PRIVILEGES ON `rsyslogdb`.* TO 'rsyslog'@'%' IDENTIFIED BY 'yourpasswordgoeshere';
flush privileges;
Leave MySQL:exit
Configure rsyslog
Setting up
How to configure rsyslog:nano /etc/rsyslog.conf
Make your #### Modules #### section the same as the following:#### MODULES ####
$ModLoad ommysql # provides support for MySQL
$ModLoad imuxsock.so # provides support for local system logging (e.g. via logger command)
$ModLoad imklog.so # provides kernel logging support (previously done by rklogd)
#$ModLoad immark.so # provides --MARK-- message capability
# Provides UDP syslog reception
$ModLoad imudp.so
$UDPServerRun 514
# Provides TCP syslog reception
$ModLoad imtcp.so
$InputTCPServerRun 514
Just above ### begin forwarding rule ### section add info similar to the following line to limit IP addresses that can send syslog info to the server, for each class C subnet the server will be collecting from, you'll need to enter the subnet info followed by /24 (such as 172.18.22.0/24) to allow that subnet to send syslog data. Alternatively, you can limit by single IP addresses. The 127.0.0.1 is necessary so the server can send logs to itself:$AllowedSender TCP, 127.0.0.1, 172.18.22.0/24
$AllowedSender UDP, 127.0.0.1, 172.18.22.0/24
Add the following line to the ### begin forwarding rule ### section. Replace the "<yourrsyslogpasswordhere>" bit with the password you set for rsyslog MySQL user above:*.* :ommysql:127.0.0.1,rsyslogdb,rsyslog,<yourrsyslogpasswordhere>
When done modifying the file, hit Ctrl+x, then y and then enter to save the file.
Restart the rsyslog service:service rsyslog restart
Test rsyslog
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 SystemEvents;
If you see anything other than “empty set” it’s working. Exit out of MySQL:exit
Configure Apache
Configure CentOS to start the web server at bootup and manually start the service:chkconfig --levels 235 httpd on
service httpd start
modify 2 lines to match your server's respective ip and fqdn in /etc/httpd/conf/httpd.confnano /etc/httpd/conf/httpd.conf
from:
Listen 80
to:
Listen ip.address.of.server:80
and from:
#ServerName www.example.com:80
to:
ServerName fully.qualified.domian.name:80
Hit CTRL+x, then Y and then enter to save and exit the file.
Restart the server:/etc/init.d/httpd restart
Set up IPTables
Edit the iptables file:nano /etc/sysconfig/iptables
Add these lines to the /etc/sysconfig/iptables file (before the COMMIT line):-I INPUT -p tcp --dport 80 -m state --state NEW,ESTABLISHED -j ACCEPT
-I OUTPUT -p tcp --sport 80 -m state --state ESTABLISHED -j ACCEPT
You'll need to enter lines similar to the following based on your network environment. For more info on how to use IPTables in CentOS see
http://wiki.centos.org/HowTos/Network/IPTables:-I INPUT -p tcp --dport 514 -s 172.18.22.0/24 -j ACCEPT
-I INPUT -p udp --dport 514 -s 172.18.22.0/24 -j ACCEPT
Restart the network service and IPTables:/etc/init.d/network restart
/etc/init.d/iptables restart
Configure LogAnalyzer
Install LogAnalyzer
Check for the latest stable release by going to
http://loganalyzer.adiscon.com/downloads in a browser. Current latest release is
http://loganalyzer.adiscon.com/downloads/loganalyzer-3-4-2-v3-stable
Download it on your CentOS server by doing the following:cd /tmp
wget http://download.adiscon.com/loganalyzer/loganalyzer-3.4.2.tar.gz
Uncompress the file:tar -xvzf loganalyzer-3.4.2.tar.gz
Copy the source directory to the Apache html directory and create config.php file:cd loganalyzer-3.4.2/src
rm -R -f /var/www/html
mkdir /var/www/html
cp -R * /var/www/html
cd /tmp/loganalyzer-3.4.2/contrib/
cp * /var/www/html
cd /var/www/html
chmod +x configure.sh secure.sh
./configure.sh
The last line will create a blank “config.php” file, and will give everyone write access to it. It won´t generate any output.
Check if the config.php file has been created (initial setup via browser will make changes to this file):ls
Create LogAnalyzer MySQL user and database: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
Initial setup of Log Analyzer, Step One:
On a client system go to the Log server's URL using a web browser (http://yoursystemnamehere.blah.org).
A message stating "Critical Error Occurred: Error main configuration file is missing! Click here to install Adiscon LogAnalyzer!" will appear in browser. Click on the word "here" to start the install.
Click "Next" twice and you should get to the "Basic Configuration" screen. The recommend settings are:
Number of syslog messages per page: 200 (set this lower if the log server is on a slow system)
Message character limit for main view: 80 (default)
Character display limit for all string fields: 80
Show message details popup: Yes (default)
Automatically resolved IP Addresses (inline): Yes (default)
Enable User Database: Yes
Database Host: localhost (default)
Database Port: 3306 (default)
Database Name: loganalyzerdb
Table prefix: logcon_
Database User: loganalyzer
Database Password: <enter in the loganalyzer database user password that you set earlier here>
Require user to be logged in: Yes
Click "Next".
Initial setup of Log Analyzer, Step Two:
Click "Next" on the "Create Tables" page, then click "Next" on the "Check SQL Results" page and then set up the admin user:
Username: <enter in the username here that you want>
Password: <enter in the user password that you want to use>
Repeat Password: <re-enter in the user password that you want to use>
Click "Next".
Initial setup of Log Analyzer, Step Three:
The recommended settings for the "Create the first source for syslog messages" page are:
Name of the source: All Syslog Sources
Source type: MySQL Native
Select view: Syslog Fields (default)
Table type: MonitorWare
Database host: localhost (default)
Database name: rsyslogdb
Database table name: SystemEvents
Database user: rsyslog
Database password: <enter in the rsyslog database user password that you set earlier here>
Enable row counting: "Yes"
Click "Next" and then click "Finish".
The install of LogAnalyzer has now been completed. Now other users can be created and there are many settings that can be tweaked as needed.
Point all of the syslog capable devices to the new log server and begin analyzing the aggregated logs
