Ahhh… how to keep track of all of those logs. You could go fabulous and use Splunk (or one of splunks alternatives), 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:
If you want to see fancy graphs:
apt-get install php5-gd |
Fancy graphs:
