Rsyslog central logging with LogAnalyzer

Rsyslog central logging with LogAnalyzer

After reaching a number of managed servers a central log-collecting and -analyzing solution is inevitable.

As most Linux systems come with rsyslog by default, it’s an easy way to use it to redirect some or all our logs to a central log server.

We’ll need only a few modifications on the client side, and the setup of an rsyslogd server on a new vps or on one of our current servers.

Client setup

Though we may send all our logs, let’s think it over and send only the logs that we would like to analyze later. I’d recommend to send anything at or above Notice level. Some services log useful things on Nhte Notice level as well.

On the client side the only modification we need is setting the following in /etc/rsyslog.d/client.conf:

  • a log template to use for forwarding
  • a <send_pattern> of what to send – I recommend *.emerg;*.alert;*.crit;*.err;*.warning;*.notice
  • the <server_ip> of the rsyslogd server
  • our distinctive hostname <my_hostname> – there’s nothing more annoying to see all entries ont he logserver with source ’localhost’ or ’web’ J

Configuration file for the client – /etc/rsyslog.d/client.conf

# An "In-Memory Queue" is created for remote logging.
$WorkDirectory /var/spool/rsyslog    # where to place spool files
$ActionQueueFileName queue      # unique name prefix for spool files
$ActionQueueMaxDiskSpace 1g     # spool space limit (use as much as possible)
$ActionQueueSaveOnShutdown on   # save messages to disk on shutdown
$ActionQueueType LinkedList     # run asynchronously
$ActionResumeRetryCount -1      # infinety retries if host is down

# Use default format
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat

$template ForwardFormat," <%PRI%>%TIMESTAMP:::date-rfc3339% <my_host_name> %syslogtag:1:32%%msg:::sp-if-no-1st-sp%%msg%"

# Sending logs that match <send_pattern> to <server_ip> via UDP on 514 using the custom format fith the clientcert as hostname.
<send_pattern> @<server_ip>:514;ForwardFormat

# Logging locally.
# Log auth messages locally
auth,authpriv.*                 /var/log/auth.log
# First some standard log files.  Log by facility.
*.*;auth,authpriv.none         -/var/log/syslog
cron.*                          /var/log/cron.log
daemon.*                       -/var/log/daemon.log
kern.*                         -/var/log/kern.log
#lpr.*                          -/var/log/lpr.log
mail.*                         -/var/log/mail.log
user.*                         -/var/log/user.log

# Logging for the mail system.  Split it up so that
# it is easy to write scripts to parse these files.
#                      -/var/log/
mail.warn                      -/var/log/mail.warn
mail.err                        /var/log/mail.err

# Logging for INN news system.
news.crit                       /var/log/news/news.crit
news.err                        /var/log/news/news.err
news.notice                     -/var/log/news/news.notice

# Some "catch-all" log files.
       news.none;mail.none     -/var/log/debug
       mail,news.none          -/var/log/messages

we should restart rsyslog with the new config

service rsyslog restart

Rsyslogd Server

The rsyslogd server consists of these parts:

  • rsyslogd – receiving logs on port udp 514 (must be opened on the firewall)
  • MySQL DB – storing the logs
  • LoganAlyzer – reading the DB and easy-filtering on the entries. It also provides some fancy colorful statistics

Install the necessary packages

apt-get install php5 mysql-server php5-mysql apache2 rsyslog-mysql php5-gd 

Create the Syslog DB

The appropriate DB script can be optained from Adiscon’s website (provider of LogAnalyzer)


USE Syslog;
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
mysql -p < createDB.sql

rsyslog MySQL user

We’ll create a Mysql user that only has access to the Syslog database:

mysql –p
GRANT ALL ON Syslog.* TO 'rsyslog'@'localhost' IDENTIFIED BY '<db_password>’;

Configure to receive logs

Don’t forget to open port udp 514 on the firewall – clients are sending their logs onto this port.

As the database is already configured, we only need a server.conf in /etc/rsyslog.d – set the correct <db_password>


#Load UDP module
$ModLoad imudp

# Use traditional timestamp format.
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat

# Switch to remote ruleset
$RuleSet remote

# database template that separates the process ID from the syslog tag
$template dbFormat,"insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, processid) values ('%msg%', %syslogfacility%, '%HOSTNAME%',%syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:(.+)(\[[0-9]{1,5}\]).*--end%', '%syslogtag:R,ERE,1,BLANK:\[([0-9]{1,5})\]--end%')",sql

# Log all received to MySQL
$ModLoad ommysql
*.* :ommysql:localhost,Syslog,rsyslog,<db_password>;dbFormat

# Switch back to default ruleset
$RuleSet RSYSLOG_DefaultRuleset

$InputUDPServerBindRuleset remote
$UDPServerRun 514
$UDPServerAddress * 

Install LogAnalyzer

Once Clients are logging to the DB, let’s have a better solution than typing SELECT statements to analyze our logs.

I had some issues with the 4.x (beta) version on LogAnalyzer, so let’s go with the latest stable 3.6.6

Download the tarball, move it’s contents to Apache’s documentroot:

tar xzf loganalyzer-3.6.6.tar.gz
mv loganalyzer-3.6.6/src /var/www/html/loganalyzer

Configure LogAnalyzer

touch /var/www/html/loganalyzer/config.php
chown www-data. /var/www/html/loganalyzer/config.php
chmod 777 /var/www/html/loganalyzer/config.php

Navigate to http://<mydomain>/loganalyzer/install.php

  • at step 3 provide the database details
  • in step 6 create the admin account
  • in step 7 fill in the details of the rsyslog database

That’s all, Your Rsyslog server is ready for use.

Using LogAnalyzer

Now, that our clients are sending logs to the rsyslogd server, let’s take a look at our LogAnalyzer.

Visit and login with your admin account

You’ll see something similar – hopefully with only a few ERR entries:

You can set custom filters also. It is easy to exclude source, syslogtag, severity from the search by clicking on the values in the entries, but after a while one gets used to writing these filters, which is much more professional 😉

For example a filter for warning and higher severity entries within the last 1 hour for the host sent with syslogtag sudo: looks like this

severity:0,1,2,3,4 datelastx:1 syslogtag:=sudo:

We may also have nice graphs to see trends, the most severe syslogtags, etc. They may also amaze the management, but be careful that  they like to see all the colors but red, and the slope should also have a positive steepness (values are less important) 😛

Custom graphs may be created in Admin Center -> Charts, filters can be applied on graphs as well

A custom graph showing the most severe Syslogtags we had so far:

DB Cleanup

After a few weeks (or sooner with extensive logging) LogAnalyzer may slow down.

To handle this situation we should get rid of old and/or unimportant logs.

For example, I usually delete all Notice entries from the DB which are older than 3 days

delete FROM SystemEvents WHERE Priority=”5″ and ReceivedAt < DATE_SUB(NOW(), INTERVAL 3 day);

As I mentioned above, I only send severity 1-5 messages. Collecting Info and Debug messages would fill up the logserver’s DB really quickly.

Leave a Reply

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

You are commenting using your 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