rsyslog + MySQL + LogAnalyzer on OpenSUSE

By aidenSystem Engineering
rsyslog Features Map

rsyslog Features Map

1. Install requirements

zypper install rsyslog rsyslog-module-relp rsyslog-module-mysql rsyslog-module-snmp rsyslog-module-mmnormalize

2. Create RuleBase for PHP log

vi /etc/rsyslog.d/phplog.rulebase

# PHP  Logs
rule=: %server:char-to:\x3a%\x3a PHP %priority:char-to:\x3a%\x3a%message:rest%
rule=: PHP %priority:char-to:\x3a%\x3a%message:rest%

3. Prepare MySQL

CREATE DATABASE `syslog`;
USE `syslog`;

CREATE USER 'syslog'@'localhost' IDENTIFIED BY 'my_password';
GRANT EXECUTE, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE
  ON syslog.* TO 'syslog'@'localhost';
FLUSH PRIVILEGES;

CREATE TABLE `SystemEvents` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `CustomerID` bigint(20) DEFAULT NULL,
  `ReceivedAt` datetime DEFAULT NULL,
  `DeviceReportedTime` datetime DEFAULT NULL,
  `Facility` smallint(6) DEFAULT NULL,
  `Priority` smallint(6) DEFAULT NULL,
  `FromHost` varchar(60) DEFAULT NULL,
  `Message` text,
  `NTSeverity` int(11) DEFAULT NULL,
  `Importance` int(11) DEFAULT NULL,
  `EventSource` varchar(60) DEFAULT NULL,
  `EventUser` varchar(60) DEFAULT NULL,
  `EventCategory` int(11) DEFAULT NULL,
  `EventID` int(11) DEFAULT NULL,
  `EventBinaryData` text,
  `MaxAvailable` int(11) DEFAULT NULL,
  `CurrUsage` int(11) DEFAULT NULL,
  `MinUsage` int(11) DEFAULT NULL,
  `MaxUsage` int(11) DEFAULT NULL,
  `InfoUnitID` int(11) DEFAULT NULL,
  `SysLogTag` varchar(60) DEFAULT NULL,
  `ProcessID` varchar(60) DEFAULT NULL,
  `EventLogType` varchar(60) DEFAULT NULL,
  `GenericFileName` varchar(60) DEFAULT NULL,
  `SystemID` int(11) DEFAULT NULL,
  `Checksum` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ie1_SystemEvents` (`DeviceReportedTime`),
  KEY `ie2_SystemEvents` (`FromHost`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELIMITER $$
ALTER DEFINER=`root`@`%` EVENT `evt_purge_old_events` 
ON SCHEDULE EVERY 1 DAY STARTS '2010-01-01 06:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    DELETE FROM syslog.SystemEvents WHERE DeviceReportedTime < SUBDATE(CURRENT_TIMESTAMP, INTERVAL 3 MONTH);
END$$
DELIMITER;

4. rsyslog Configuration for Log-Servers

######################
# Aiden (server)
######################

module(load="mmnormalize")
module(load="imrelp")
module(load="imtcp" keepalive="on")
module(load="imudp")
module(load="ommysql")

input(type="imrelp" port="2514" keepalive="on" ruleset="remote")
input(type="imtcp" port="1514" ruleset="remote")
input(type="imudp" port="514" ruleset="remote")
$AllowedSender TCP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24
$AllowedSender UDP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24

template(name="aidenSQLformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values (TRIM('%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%')")
template(name="aidenSQL4PHPformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%$!msg%', %$!facility%, '%hostname%', %$!syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%$!syslogtag%', '%syslogtag:R,ERE,1,BLANK:\\[([0-9]{1,5})\\]--end%')")

ruleset(name="php2db") {
	if not ($syslogtag contains_i 'suhosin') then {
		action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase")
		if ($parsesuccess == 'OK') then {
			set $!syslogtag = 'php';
			set $!facility = 3;
			set $!msg = 'PHP ' & $!priority & ':' & $!message;
			if ($!priority contains_i 'error') then {
				set $!syslogpriority = 3;
				action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306")
			}else if ($!priority contains_i 'warning') then  {
				set $!syslogpriority = 4;
				action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306")
			}else if ($!priority contains_i 'notice') then {
				set $!syslogpriority = 5;
				action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306")
			}
		}
	}
	if ($fromhost-ip == '127.0.0.1') then action(type="omfile" file="/var/log/php.log")
}
ruleset(name="remote") {
	if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \
			$syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then {
		call php2db
	}else{
		action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306")
	}
	if ($fromhost-ip != '127.0.0.1') then action(type="omfile" file="/var/log/remote")
	stop
}

#--------------

if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug
	-/var/log/messages
	stop
}
if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug
	-/var/log/messages
	stop
}
if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug
	-/var/log/auth.log
	stop
}

if  ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \
	   $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then {
	call php2db
	stop
}

*.=warning;*.=err;*.=crit   action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306")
if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \
	action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306")

if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then {
	-/var/log/auth.log
	stop
}
auth,authpriv.*				-/var/log/auth.log
& stop
cron.*					-/var/log/cron.log
& stop
if ($msg contains_i 'pam_unix(crond:session)') then {
	-/var/log/cron.log
	stop
}
local5.*				-/var/log/sftpd.log
& stop
if ($syslogtag contains_i 'internal-sftp') then {
	-/var/log/sftpd.log
	stop
}
if ($syslogtag contains_i 'tomcat') then {
	-/var/log/tomcat.log
	stop
}

######################

5. rsyslog Configuration for Log-Client

######################
# Aiden (client)
######################

module(load="mmnormalize")
module(load="omrelp")

ruleset(name="php2remote") {
	if not ($syslogtag contains_i 'suhosin') then {
		action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase")
		if ($parsesuccess == 'OK') then {
			if ($!priority contains_i 'error' or $!priority contains_i 'warning' or $!priority contains_i 'notice') then  {
				action(type="omrelp" target="logserver_ip_or_name" port="2514")
			}
		}
	}
	action(type="omfile" file="/var/log/php.log")
}

#--------------

if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug
	-/var/log/messages
	stop
}
if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug
	-/var/log/messages
	stop
}
if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug
	-/var/log/auth.log
	stop
}

if  ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \
	   $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then {
	call php2remote
	stop
}

*.=warning;*.=err;*.=crit	action(type="omrelp" target="logserver_ip_or_name" port="2514")
if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \
	action(type="omrelp" target="logserver_ip_or_name" port="2514")

if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then {
	-/var/log/auth.log
	stop
}
auth,authpriv.*				-/var/log/auth.log
& stop
cron.*					-/var/log/cron.log
& stop
if ($msg contains_i 'pam_unix(crond:session)') then {
	-/var/log/cron.log
	stop
}
local5.*				-/var/log/sftpd.log
& stop
if ($syslogtag contains_i 'internal-sftp') then {
	-/var/log/sftpd.log
	stop
}
if ($syslogtag contains_i 'tomcat') then {
	-/var/log/tomcat.log
	stop
}

######################

6. Install LogAnalyzer

cd /usr/local/src
wget http://download.adiscon.com/loganalyzer/loganalyzer-4.1.3.tar.gz
tar -zxvf loganalyzer-4.1.3.tar.gz
mkdir -p /srv/www/htdocs/loganalyzer
cp /usr/local/src/loganalyzer-4.1.3/src/* /srv/www/htdocs/loganalyzer/

7. LogAnalyzer Configuration

http://localhost/loganalyzer

 

♦ References
http://www.the-art-of-web.com/system/rsyslog-config/
http://www.liblognorm.com/files/manual/configuration.html

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.