Installing Rsyslog With a MySQL and Loganalyzer

Ahhh… how to keep track of all of those logs. You could go fabulous and use Splunk, but if you want some free and simple analysis with not a lot of extra load, rsyslog dumping into a database with Loganalyzer is a pretty decent option. For an introduction to rsyslog, see this video of Rainer Gerhards. This particular GNU/Linux distro is Knoppix 7.2 copied to a hard drive, so it is *weird*. But, it works well for many things and gives you a very complete set of tools with not a lot of fuss. Mostly it works like Debian or Ubuntu. Let’s get on with it. Install ryslog-mysql using apt-get (or perhaps yum on a RH derived distro):

root@sal:/home/knoppix# apt-get install rsyslog-mysql
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following extra packages will be installed:
  rsyslog
Suggested packages:
  rsyslog-doc rsyslog-gnutls rsyslog-gssapi rsyslog-relp
The following packages will be REMOVED:
  klogd sysklogd
The following NEW packages will be installed:
  rsyslog rsyslog-mysql
0 upgraded, 2 newly installed, 2 to remove and 200 not upgraded.
Need to get 670 kB of archives.
After this operation, 1,206 kB of additional disk space will be used.
Do you want to continue [Y/n]?Y

Grab a copy of Loganalyzer

You’ll also want to grab a copy of the rsyslog source of around the time of the package. I couldn’t find 5.8.11, but 5.8.12 is out there, and the stars say that 5.8.11 is close enough. So, if you look here:
rsyslog-5.8.12/plugins/ompgsql/
You’ll see a script called createDB.sql that you need to run to populate the correct tables for rsyslog. When we ran this without modification, we got this:

mysql < createDB.sql -u root 
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use near 
''Syslog' WITH ENCODING 'SQL_ASCII'' at line 1

Let’s modify the script:

CREATE DATABASE 'Syslog';
Use Syslog;


CREATE DATABASE Syslog;
USE Syslog;
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
);

After running this script against the database, the correct tables are there:

root@sal:/home/knoppix# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.5.31-0+wheezy1 (Debian)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use Syslog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+
2 rows in set (0.00 sec)

We need to modify /etc/rsyslog.conf to listen on 514 TCP and UDP and write all entries to the DB. This works for us:

#rsyslog v3 config file

# if you experience problems, check
# http://www.rsyslog.com/troubleshoot for assistance
$EscapeControlCharactersOnReceive off

#### MODULES ####
$ModLoad ommysql

$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


#### GLOBAL DIRECTIVES ####

# Use default timestamp format
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat

# File syncing capability is disabled by default. This feature is usually not required, 
# not useful and an extreme performance hit
#$ActionFileEnableSync on


*.*	:ommysql:localhost,Syslog,root,

# Log all kernel messages to the console.
# Logging much else clutters up the screen.
#kern.*                                                 /dev/console

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
# I commented this to log everything -srh
#*.info;mail.none;authpriv.none;cron.none                /var/log/messages

# The authpriv file has restricted access.
authpriv.*                                              /var/log/secure

# Log all the mail messages in one place.
mail.*                                                  /var/log/maillog


# Log cron stuff
cron.*                                                  /var/log/cron

# Everybody gets emergency messages
*.emerg                                                 *

# Save news errors of level crit and higher in a special file.
uucp,news.crit                                          /var/log/spooler

# Save boot messages also to boot.log
local7.*

Restart rsyslog:

root@sal:/etc# service rsyslog restart
[ ok ] Stopping enhanced syslogd: rsyslogd.
[ ok ] Starting enhanced syslogd: rsyslogd.
root@sal:/etc#

Are we getting new entries yet?

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 5.5.31-0+wheezy1 (Debian)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> use Syslog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> select * from SystemEvents limit 1;
.
.
.  | DeviceReportedTime  | Facility | Priority | FromHost | Message 
| NTSeverity | Importance | EventSource | EventUser | EventCategory
.
.
.
| 2013-10-22 20:34:32 | 2013-10-22 20:34:32 |        0 |        6 | sal      | imklog 5.8.11, lo
.
.
.
1 row in set (0.00 sec)

mysql>

Yes. We. Are. Take the Loganalyzer source and put it in your web server directory. We have a pretty generic location:

var/www/loganalyzer-3.6.5/src# mkdir /var/www/loganalyzer
var/www/loganalyzer-3.6.5/src# mv * /var/www/loganalyzer
var/www/loganalyzer-3.6.5/src# 

Modify the permissions as you see fit. The web server will need to initially write to config.php. Browse to http://path/to/web, and you should see:

Error, main configuration file is missing!

Click here to Install Adiscon LogAnalyzer!

In the setup choose MYSQL Native with Syslog Fields. After the setup finishes, modify config.php using values appropriate for your configuration:

$CFG['Sources']['Source1']['DBServer'] = 'localhost';
$CFG['Sources']['Source1']['DBName'] = 'Syslog';
$CFG['Sources']['Source1']['DBUser'] = 'whateveruseryoulike';
$CFG['Sources']['Source1']['DBPassword'] = 'whateverpasswordyoulike';
$CFG['Sources']['Source1']['DBTableName'] = 'SystemEvents';

Browse, and you should now see Loganalyzer goodness:
loganalyzer

If you want to see fancy graphs:

apt-get install php5-gd

Fancy graphs:
graphlog