Hybrid Cloud Hosting Blog

Manage and analyse device logs for rapid fault finding and repair

Mar 15

Posted March 9, 2015 by  Chris Lewis


Setting up a log server to collect device logs ensures you always have the information you need to find and fix a fault or failure quickly. Here we describe how to do it using MySQL and Log Analyzer.

In this information technology powered world in which we live, we've grown to expect instant gratification, constant uptime and ubiquitous availability. Hence when there is a problem, it is greeted with ultimate scorn as customers go flocking to competitor sites, or to social networks to vent about the unavailability of your service. Meanwhile, your support phones ring off the hook. Time is therefore of the essence, always

In the face of this it's imperative that your business arms itself with the tools to find and fix faults as quickly as possible. When you are confronted with fault, device logs are an invaluable asset 

Download our ebook, 5 More Advantages of Private Cloud, to discover other ways  it can benefit your business

The problem with device logs

With either a Windows or a Linux server, all the logs are saved on the internal hard disk. With other devices, such as network switches and firewalls, however, there is rarely much internal storage available. In fact, many such devices only have sufficient internal memory to store logs for the past minute. By the time you come to track a fault on these devices, the relevant logs are long gone.

To retain these valuable logs for any longer period of time, it is necessary to export them out of the internal memory, as they are created, to an external server with sufficient storage for logs from all devices. Once the logs are present on this dedicated server you can also transpose them into a searchable database and use freely available software to rapidly interrogate the otherwise inscrutable content.

With the data from all your devices in one place, even if those devices are shutdown or unresponsive, you can still view the logs and compare the logs of multiple devices to look for patterns or chains that may have led to incidents. 

Build your own log analysis rig

This guide shows you have to prepare a server to receive the log data, export and save the data into a MySQL database and use Log Analyzer to view your logs. This guide will call for you to set-up both a web application and a database application. Both can be installed on the same server or on different ones – it’s up to you. You could also have multiple DB servers pointing to the same web application server.

There are five steps required to create your rig:

  1. Prerequisites (all servers)
  2. Setup rsyslog (all servers)
  3. Make rsyslog log to MySQL (web or DB)
  4. Setup loganalyzer web server (web only)
  5. Log Devices to Syslog server (devices)

1. Prerequisites

Note: The following pre-requisites and sources are taken from the Log Analyser installation guide available from the developer, Adiscon. For the purposes of this blog, however, we have attempted to streamline these instructions.

Install PGP Key into the apt system:

apt-key adv --recv-keys  --keyserver keyserver.ubuntu.com AEF0CF8E

Edit sources.list line  vi /etc/apt/sources.list and add following lines:

# Adiscon repository

Update apt cache

apt-get update && apt-get upgrade

Install Rsyslog

apt-get install rsyslog 

Install dependencies (these are all the dependencies like: php5 (so that apache can read .php files), mysql, etc.:

apt-get install rsyslog rsyslog-mysql unzip zip binutils cpp fetchmail flex gcc libc6-dev 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 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 

Please note: When you install dependencies, the system will ask you to enter a password for MySQL.  Enter the credentials you wish to use. It might prompt you again and ask if you want to 'configure database now?' Click yes and it will configure the database automatically. It will ask you to enter a username and password; you can set this as you like but for the rest of this post we shall assume that the admin username for mysql is ‘root’.

2. Setup rsyslog:

Note: The following instructions demonstrate how we recommend setting up syslog to receive logs from other devices and push that data to a MySQL database, and how to configure logrotation. More information and additional configuration options can be found at www.rsyslog.com.

Edit syslog to receive logs

Vi /etc/rsyslog.conf
# provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514
#$ActionQueueType LinkedList
#$ActionQueueFileName fileforward
$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
# provides TCP syslog reception
$ModLoad imtcp $InputTCPServerRun 514 $ActionQueueType LinkedList
$ActionQueueFileName fileforward $ActionResumeRetryCount -1 #infinite
$ActionQueueSaveOnShutdown on

Make syslog file readable to website

Chmod 644 /var/log/syslog

Edit logrotate to make sure this continues

Vi /etc/logrotate.conf

Modify create line to:

create 644 root

Edit logrotate to keep logs forever

vi /etc/logrotate.d/rsyslog

remove the line: rotate 7 

Note: By default, logrotate will keep logs for 7 days before deleting them. For log analysis purposes, it’s advisable to keep logs for a longer period of time. To do this you can change the number in the line above to a higher value or delete the line altogether to keep logs forever. Storage space limitations can be mitigated by removing old logs from the database, by the retention schedule, and storing them in file format. Logs in file format take up significantly less space than those in the database. You still need to keep an eye on disk space to make sure you don’t run out however.

File syslog is now ready to receive results

3. Make rsyslog log to MySQL

Edit syslog to log to MySQL

Vi /etc/rsyslog.conf

Add these lines

 $ModLoad ommysql
 *.* :ommysql:,Syslog,rsyslog,enterpassword

Use your own password in place of 'enterpassword' 

Create DB in MySQL

Mysql -u root -p

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


Create rsyslog user

GRANT ALL ON Syslog.* TO 'rsyslog'@'IP of server' IDENTIFIED BY 'enter password'; - Main GUI - replace IP of server with the public IP, and enter password with your password you used earlier
GRANT ALL ON Syslog.* TO 'rsyslog'@'localhost' IDENTIFIED BY 'enter password'; - localhost -  replace enter password with your password you used earlier
Flush privileges;

Test user

Mysql -u rsyslog -p
Use Syslog
Show tables;

Tweak retention of entries on DB:

Mysql -u root -p
 use Syslog;
set global event_scheduler = ON;

Make MySQL available publicly:

Vi /etc/mysql/my.cnf

Locate the line:

- bind-address =

Comment it out, by adding a hash, to allow connections on interfaces other than loopback:

#bind-address =

Service mysql restar 

4. Install and configure Log Analyzer:

Locate the latest version of Log Analyzer at http://loganalyzer.adiscon.com/downloads/

Right click the 'Download file name' and copy the hyperlink.

On server install:

Cd /home/user - use your user home
Wget (paste download link)
Tar -xvzf loganalyzer*
Mv loganalyzer* /var/www/
Cd /var/www
Chown www-data:www-data * . -Rf
Mv loganalyzer* loganalyzer # changes name of file for easier login
Cd loganalyzer/contrib
Cp * ./../src/
Cd ./../src/
Sh ./configure.sh

Modify apache route:

Vi /etc/apache2/sites-enabled/000-default.conf

Modify this line

DocumentRoot /var/www/loganalyzer/src/

Setup website:

Browse to IP/install.php
Make changes as necessary, and tick enable user DB
User local host
Port 3306
DB: Syslog
Leave table prefix
User: rsyslog
Password: password you used earlier
Require user to be logged in = yes
Create account:
user: Password - this can be different from before, this is to login to Log Analyzer.

Create sources

By default, Log Analyzer will create a source using the local syslog files. We however want to create sources using our SQL data. This is described in the section below on using Log Analyzer.

5. Log Devices to database server

On other servers add this line to rsyslog.conf to log to DB server

*.* @@IP (IP of your log analyzer)

On switches add this line to log to DB server:

logging IP (IP of your log analyzer)

DB Maintenance:

Due to a limitation of MySQL, the ibdata1 database file will continue to grow in size despite deletion of old records.  The only way to fix this is to back-up the DB, stop SQL, delete the files, start SQL then reimport the DB.

mysqldump -u root -p --all-databases > /home/user/alldb.sql - use your user home
service mysql stop
rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile0
rm /var/lib/mysql/ib_logfile1
service mysql start
mysql -u root -p < /home/user/alldb.sql

We recommend that you perform this process at least quarterly, within an acceptable maintenance window, and as often as is necessary to keep your ibdata file within the size of your DB server.

Using Log Analyzer

Upon first use you will need to add your source database or file to the Log Analyzer config. Login and navigate to the ‘admin centre’ and then ‘sources’.


Select add new source and enter the information shown below replacing the password and source with the ones you set-up earlier.


You can then view and filter the contents of your log database as shown below (sensitive information has been removed).


 Ensuring application uptime in the cloud


Topics: Colocation & Infrastructure