Centralized RSYSLOG Server Monitoring

This tutorial provides step-by-step instructions on how to install RSYSLOG server on Debian 6 (Squeeze) and how to send syslog data to it from multiple client servers. We will also show how to install an Apache webserver and a MySQL database server to gather the syslog data and how to install LogAnalyzer to easily browse the collected data with a web-browser.

I'm using an readymade OpenVZ virtualisation template for this setup but this should also work on most Debian based operating Systems.

I do not issue any guarantee that this will work for you!

 

Installing RSYSLOG Server

This first part discribes how to build RSYSLOG server that will gather the syslog data from it's clients. In this example our server is called rsyslog.domain.com and it has a fixed IP address of 192.168.0.15.

First we install some dependencies.

apt-get update
apt-get upgrade

apt-get install rsyslog rsyslog-mysql unzip zip binutils cpp fetchmail flex gcc libarchive-zip-perl libc6-dev libcompress-zlib-perl libpcre3 libpopt-dev lynx m4 make ncftp nmap openssl perl perl-modules zlib1g-dev autoconf automake1.9 libtool bison autotools-dev g++ mysql-server mysql-client libmysqlclient15-dev apache2 apache2-doc apache2-mpm-prefork apache2-utils libexpat1 ssl-cert libdb4.6-dev libapache2-mod-php5 php5 php5-common php5-curl php5-dev php5-gd php5-idn php-pear php5-imagick php5-imap php5-json php5-mcrypt php5-memcache php5-mhash php5-ming php5-mysql php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy php5-xmlrpc php5-xsl

Create the MySQL server password when the installation asks you to enter it!

My OpenVZ template all ready had this so this command may not be nessecary for you either...

apt-get install linux-kernel-headers

Make sure the services are up and running...

/etc/init.d/rsyslog restart
/etc/init.d/mysql restart
/etc/init.d/apache2 restart

Check that the server is listening on the proper TCP IP ports. (80 & 3306). RSYSLOG does not yet listen on any port at this time.

rsyslog:~# netstat -tapn

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      415/mysqld        
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      581/apache2

Then we can create the rsyslog database:

mysqladmin -u root -p create rsyslog
Next we start the MySQL command shell and create the rsyslog user:
mysql -u root -p

GRANT SELECT, INSERT, UPDATE, DELETE ON rsyslog.* TO 'rsyslog'@'localhost' IDENTIFIED BY 'ENTER-YOUR-NEW-RSYSLOG-PASSWORD-HERE';

FLUSH PRIVILEGES;
quit

Next we configure the rsyslog server to listen on TCP port 514:

vi /etc/rsyslog.conf

Add these lines... (remember to change your password to the one you entered when you created your MySQL server's rsyslog user.)

$ModLoad MySQL
*.*       >127.0.0.1,rsyslog,rsyslog,ENTER-YOUR-NEW-RSYSLOG-PASSWORD-HERE

...and remove the comments in front of the lines that deal with the TCP syslog reception.

# /etc/rsyslog.conf Configuration file for rsyslog v3.
#
# For more information see
# /usr/share/doc/rsyslog-doc/html/rsyslog_conf.html
$ModLoad MySQL
*.* >127.0.0.1,rsyslog,rsyslog,ENTER-YOUR-NEW-RSYSLOG-PASSWORD-HERE
#################
#### MODULES ####
#################
$ModLoad imuxsock # provides support for local system logging
$ModLoad imklog # provides kernel logging support (previously done by rklogd)
#$ModLoad immark # provides --MARK-- message capability
# provides UDP syslog reception
#$ModLoad imudp
#$UDPServerRun 514
# provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514
###########################
#### GLOBAL DIRECTIVES ####
###########################

And restart rsyslog service.

/etc/init.d/rsyslog restart

Check that the server is listening on the proper TCP IP ports. (80, 514 & 3306.)

rsyslog:~# netstat -tapn

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:514             0.0.0.0:*               LISTEN      618/rsyslogd   
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      415/mysqld        
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      581/apache2

Then we download the LogAnalyzer and configure the Apache web server to display the logs.

cd /tmp

wget http://download.adiscon.com/loganalyzer/loganalyzer-3.4.1.tar.gz

tar xvzf loganalyzer-3.4.1.tar.gz
mv loganalyzer-3.4.1/ /var/www/
cd /var/www

Then we configure www folders user rights for Apache web server.

chown www-data:www-data * . -Rf

And then we do some 'make nice' work on the LogAnalyzer folders.

mv loganalyzer-3.4.1/ loganalyzer
cd contrib/
cp * ./../src/
cd ./../src/
sh ./configure.sh

And we are ready to enter the final setup of LogAnalyzer using our web-browser. Point your web-browser to the fixed IP address of our rsyslog server that in this demo is http://192.168.0.15/loganalyzer/src/install.php

Go trough the simple setup script (it's pretty much... next -> next.)

Now you should have working rsyslog server with LogAnalyzer up and running.

Next we configure RSYSLOG clients to send their syslog data to the rsyslog server:

 

Configuring RSYSLOG Clients

Usually all we need to do is configure the rsyslog.conf file and restart the service. (Almost all Debians come with rsyslog preinstalled.)

vi /etc/rsyslog.conf

Add the work spool directory lines that are used in case network connectivity is lost and change your rsyslog server IP ADDRESS to 192.168.0.15. (You may also want to create the /rsyslog/work spool directory with the mkdir command.)

# provides TCP syslog reception
#$ModLoad imtcp
#$InputTCPServerRun 514
$WorkDirectory /rsyslog/work # default location for work (spool) files
$ActionQueueType LinkedList # use asynchronous processing
$ActionQueueFileName srvrfwd # set file name, also enables disk mode
$ActionResumeRetryCount -1 # infinite retries on insert failure
$ActionQueueSaveOnShutdown on # save in-memory data if rsyslog shuts down
*.* @@YOUR-RSYSLOG-SERVER-ADDRESS-HERE
###########################
#### GLOBAL DIRECTIVES ####
###########################

And restart rsyslog service.

/etc/init.d/rsyslog restart

Check that the server is connected on the proper TCP IP port (514).

[email protected]:~# netstat -tapn

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 192.168.0.100:49188     192.168.0.15:514         ESTABLISHED 13289/rsyslogd

This is what a successfull setup looks like.

Screenshot - LogAnalyzer In use

Sami Mattila
Internet-Content.europa SM OY


Switch:
+358.9.42833310
Mobile: +358.400.749692
Email: [email protected]
Shop: https://shop.internet-content.net

Site: http://www.internet-content.net
Blog: http://www.internet-content.net/en/blog

Share this page:

29 Comment(s)

Add comment

Please register in our forum first to comment.

Comments

By:

? There is no mail database in this server. Only rsyslog database.

By:

Unless the webserver user www-data is not in the adm group, phpLogCon will not be able to read the syslog file. 
groupadd logadmin
usermod -a -G logadmin www-data 

You may also have to modify the /etc/logrotate.conf configuration file. Search for the default "create" command, and change it to the following:
 create 640 root logadmin

By: RL

Unfortunately, that did not help, still struggling with that pesky "Syslog file is not readable, read access may be denied" message and no logs at all in any way.

By:

Might be good to include that also... but it should install automatically as a dependency.

By:

I believe you have posted the wrong apt-get install row.

 You miss the rsyslog-mysql package!

By:

This tutorial is "bare-bones" tutorial.

It does not deal with any security settings you probably want to implement on your own. For instance I recommend using SSL encryption for the site. I recommend using .htaccess file for login protection etc...

 

By: admin

Fixed, thanks a lot for the note.

By:

Hi.

What's the purpose of granting access to the mail database to the rsyslog user?

By:

I got a problem at step 7 from loganalyzer install:

ERROR: Failed to open the syslog file '/var/log/syslog'! Check if the file exists and LogAnalyzer has sufficient rights to it<

By: jejolb

see my comment below

By: peerx

Nice tutorial. Works well! Thank you.

command cd contrib/  did not work. But I found my way out.

Peerx

By: Pilgrim

Hello,

thank you for easy and great tutorial, but I have a problem with LogAnalyzer, after that I click to next, next, ... in the installation wizard, the analyzer show me a message "Syslog file is not readable, read access may be denied". Can you write step by step on install.php page?

By: jejolb

you need to edit syslog's permission so "others" can read it, command:

 cd /var/log/

 chmod 644 syslog

 note: for the security freaks, yes this can be a leak for security purposes. another way is to add the loganalyzer to the same usergroup  as root. dont know how to do this though

 

 

By:

what about being a little more specific about the wizard? like configuring the database, saying what is a source... and so on.

 I know it's pretty easy but can be confusing for some!

 thanks

By: Tyler Bird

"First we install some debendancies."

First we install some dependencies.

Love what you guys do here! Keep up the great work.

By:

I'm using Wheezy not Squeeze. Does anyone else see that error in /var/log/syslog ? I tried installing  rsyslog-mysql and re doing the mysql commands , but the error still occurred after  'service rsyslog restart'.

 The loganalyzer web page does work. found the error by pressing  "I'd like to feel sad" [ that is funny! ]
PS: Thank you for the tutorial. 

 

 

By: Anonymous

What I don't understand, at which part in this tutorial do we create the rsyslog tables of the Database? 
We do create the Database at
mysqladmin -u root -p create rsyslog 
right?
I'm just before the Loganalyzer installation, was curios an did a 
mysql -u rsyslog -p
mysql> use rsyslog;
mysql> show tables;

and received a
Empty set (0.00 sec)
 

Was I to curios? Will this happen with the Loganalyzer installation? 

 Regards
Ralph

By: Anonymous

How about a response to Ralph's comment?  When do the tables get built in MySQL?

By: shofer

I have got that error:

"Could not find the configured table, maybe misspelled or the tablenames are case sensitive"

 

What I did wrong ?

By: jong

I know this is a really old post but it was also the top post when I was looking for an answer to the same problem. 

 On "Step 7 - Create the first source for syslog messages" make sure your table name is camel cased like the on created in the createDB.sql script (SystemEvents, not systemevents). Loganalyzer's installer wants to default to all lower case and that doesn't match the table name. 

By: phobia

Thank you Sami, I set this up today on a Debian VM and it works great!

One note, it appears that when you install the rsyslog-mysql package in Debian now, it creates the database for you and prompts for a password.  Unfortunately not sure that it works right as I don't think the user had the correct permissions to the database from the package script, so I used your instructions to grant them anyway!

Cheers!

phobia

 

Need help with Debian GNU/Linux?   Try Efnet #debian

By: Girish KG

I have prepared an automated shell script (with the help of Sami Mattila's How To)which will install all the latest:

1. rsyslog

2. webmin,

3. PHPMyAdmin and

4. loganalyzer

 This is on Debian 7

http://cdimage.debian.org/debian-cd/7.0.0/amd64/iso-cd/debian-7.0.0-amd64-netinst.iso 

I have only tested this on "debian-7.0.0-amd64-netinst" and is working as expected

### Note that the MySQL conf for rsyslog is /etc/rsyslog.d/mysql.conf

### and it is configured by default when you install rsyslog

###                 So, no need of creating it manually.

 ######################################################

 #!/bin/bash

##### Syslog and Log analiser on Debian 7

##### This will install RSYSLOG Server with web UI

##### This assumes that installation is done on fresh debian net install version which installs and configures MySQL from scratch

##### So that no need of manually creating any DB and/or users

##### Just remember the MySQL root password for the installtion purpose


## Note: Put in you MySQL root password which you gonna use when it prompts for new installation and remember to use the same, otherwise installation will fail.

MYSQL_PASS="gfkjh867gdfk"


##### Function to prompt for user attention

function pause(){

   read -p "$*"

}

##### 1. Installing RSYSLOG Server, MySQL, Apache2 webmin and PHPMyAdmin

## First we install some dependencies.

## Installing webmin

echo "I am assuming loganalyzer-3.6.3 as latest and stable"

echo "Please terminate this script if this not the latest version and correct the same inside the script and run the script again"

echo "Press [Ctrl+C] to terminate the installation or"

pause 'Press [Enter] key to continue...'

echo "### Webmin Source########################" >> /etc/apt/sources.list

echo "deb http://download.webmin.com/download/repository sarge contrib" >> /etc/apt/sources.list

echo "deb http://webmin.mirror.somersettechsolutions.co.uk/repository sarge contrib" >> /etc/apt/sources.list

echo "Getting Key for webmin signature"

cd /root

wget http://www.webmin.com/jcameron-key.asc

apt-key add jcameron-key.asc

apt-get update

apt-get upgrade

apt-get -y install webmin vim sudo

echo "webmin, vim, sudo have been installed"

echo "Now installing packages for syslog server"


### Remove PostgreSQL if it is installed

echo "Running postgresql remover"

apt-get -y autoremove postgresql 

apt-get install rsyslog rsyslog-mysql unzip zip binutils cpp fetchmail flex gcc libarchive-zip-perl libc6-dev perl libpcre3 libpopt-dev lynx m4 make ncftp nmap openssl perl perl-modules zlib1g-dev autoconf automake1.9 libtool bison autotools-dev g++ mysql-server mysql-client apache2 apache2-doc apache2-mpm-prefork apache2-utils libexpat1 ssl-cert libdb-dev libapache2-mod-php5 php5 php5-common php5-curl php5-dev php5-gd php-pear php5-imagick php5-imap php5-mcrypt php5-memcache php5-ming php5-mysql php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy php5-xmlrpc php5-xsl libmysqlclient-dev php5-intl php5-common php5-common

apt-get install linux-kernel-headers mysql-server mysql-dev


## Install phpMyAdmin

echo "installing PhpMyAdmin"

apt-get install phpmyadmin


echo "Make sure to restart rsyslog, mysql and apache2"

/etc/init.d/rsyslog restart

/etc/init.d/mysql restart

/etc/init.d/apache2 restart


### Check that the server is listening on the proper TCP IP ports. (80 & 3306). RSYSLOG does not yet listen on any port at this time.

echo "Check that the server is listening on the proper TCP IP ports. (80 & 3306). RSYSLOG does not yet listen on any port at this time."

netstat -tapn

pause 'Press [Enter] key to continue...'


### Note that the MySQL conf for rsyslog is /etc/rsyslog.d/mysql.conf

### and it is configured by default when you install syslog

### Don't change unless you know what you are doing

#echo 'CREATE USER [email protected] IDENTIFIED BY "rsyslog";

#CREATE DATABASE IF NOT EXISTS `rsyslog` DEFAULT CHARACTER SET `utf8` COLLATE `utf8_unicode_ci`;

#GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `rsyslog`.* TO [email protected];' | mysql -u root -p$MYSQL_PASS



### Configure syslog to listen on TCP 514

### Just edit /etc/rsyslog.conf and remove the comment for "$InputTCPServerRun 514"

#read -p "Press [Enter] key to continue..."

sed -i '17i $ModLoad imudp' /etc/rsyslog.conf

sed -i '19i $UDPServerRun 514' /etc/rsyslog.conf

sed -i '23i $ModLoad imtcp' /etc/rsyslog.conf

sed -i '25i $InputTCPServerRun 514' /etc/rsyslog.conf


cat /etc/rsyslog.conf

echo "rsyslog.conf file has been modified, please look around and press [Enter] if it is fine"

echo "If you find anything wrong, then please open another SSH Session and correct the same and then come here and press [Enter]"

pause 'Press [Enter] key to continue...'

/etc/init.d/rsyslog restart


netstat -tapn

echo "Make sure syslog is listening on TCP/UDP PORT 514"

pause 'Press [Enter] key to continue...'


#### 2. Installing Log Analyzer 

#### Then we download the LogAnalyzer and configure the Apache web server to display the logs.

echo "Preparing to install loganalyzer....Assuming latest stable version is loganalyzer-3.6.3"


mkdir /var/repo

mkdir /var/repo/loganalyzer

cd /var/repo/loganalyzer

wget http://download.adiscon.com/loganalyzer/loganalyzer-3.6.3.tar.gz

tar xvzf loganalyzer-3.6.3.tar.gz

mv loganalyzer-3.6.3/ /var/www/


echo "Adding the proper access rights for apache2 for /var/www/"

cd /var/www

chown www-data:www-data * . -Rf


echo "Renaming loganalyzer-3.6.3 to loganalyzer"

mv loganalyzer-3.6.3/ loganalyzer

cd loganalyzer/contrib/


echo "Copying "configure.sh" and "secure.sh" from contrib/ to src/"

cp * ./../src/


echo "Navigating in to src/"

cd ./../src/


echo "Running "configure.sh" script"

sh ./configure.sh

echo "All set, Point your web browser to 'http://192.168.0.15/loganalyzer/src/install.php' and folow the on screen installation instructions"

#######################################

By:

For all those who are wondering when the tables are made...
The answer is, they are automatically made during the web-installation.
Make sure you enter the correct user-name and password.

Sam

By: NOU.SAVY

Dear team,

how to add remote hosts to rsyslog server ???

 

Thanks,

SAVY

By: NOU.SAVY

Dear team,

Please I meet this problem message alert:

No syslog records found - Error Details:

 

No syslog records found

how can I fix it ???

By: SamTzu

Nice to see so many people taking interest in this.For those who are experiencing problems with the "No syslog records found" try these steps.

Is the server receiving records from the clients?tail -f /var/log/syslog

Are the records ending up in the database?Use server.domain.com/phpmyadmin to browse your SystemEvents table.

By: reader

Wrong listing: [email protected]:~# netstat -tapn

By: ChrisVV

Dear SAVY,Just connet to your MySQL database, and try this:

use Syslog;

ALTER TABLE SystemEvents ADD processid varchar(60) NOT NULL DEFAULT '' AFTER Checksum;

... and reload yout Loganalyzer page ;-)

By: Fiq

Hi. Want to ask if this log monitoring was totally free and no license required ?