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
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:
- Prerequisites (all servers)
- Setup rsyslog (all servers)
- Make rsyslog log to MySQL (web or DB)
- Setup loganalyzer web server (web only)
- Log Devices to Syslog server (devices)
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:
Update apt cache
apt-get update && apt-get upgrade
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
Make syslog file readable to website
Chmod 644 /var/log/syslog
Edit logrotate to make sure this continues
Modify create line to:
create 644 root
Edit logrotate to keep logs forever
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
Add these lines
Use your own password in place of 'enterpassword'
Create DB in MySQL
Mysql -u root -p
Create rsyslog user
Tweak retention of entries on DB:
Make MySQL available publicly:
Locate the line:
- bind-address = 127.0.0.1
Comment it out, by adding a hash, to allow connections on interfaces other than loopback:
#bind-address = 127.0.0.1
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:
Modify apache route:
Modify this line
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)
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.
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).