Setup rsyslogd

yum -y install rsyslog-gnutls rsyslog-mysql rsyslog-crypto

echo "\$ModLoad imudp" > /etc/rsyslog.d/server.conf
echo "\$UDPServerRun 514" >> /etc/rsyslog.d/server.conf
echo "\$ModLoad imtcp" >> /etc/rsyslog.d/server.conf
echo "\$InputTCPServerRun 514" >> /etc/rsyslog.d/server.conf
echo "\$PreserveFQDN on" >> /etc/rsyslog.d/server.conf

MySQL/MariaDB database configuration

Assuming MariaDB is already installed and running. Hint: Make sure innodb_file_per_table = 1 is set in the MariaDB server configuration!

Database setup

Syslog Default Format

mysql < /usr/share/doc/rsyslog-*/mysql-createDB.sql
mysql -Be "ALTER TABLE Syslog.SystemEvents ENGINE=innodb DEFAULT CHARSET=UTF8 row_format=COMPRESSED KEY_BLOCK_SIZE=4"
mysql -Be "ALTER TABLE Syslog.SystemEvents ADD INDEX SyslogTag(SyslogTag);"
mysql -Be "ALTER TABLE Syslog.SystemEvents ADD INDEX FromHost(FromHost);"

Loganalyzer db setup

mysql -Be "use Syslog; DROP TABLE IF EXISTS php_log; CREATE TABLE \`php_log\` (\
      \`ID\` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,\
      \`FromHost\` varchar(100) NOT NULL,\
      \`Priority\` int(2) NOT NULL,\
      \`Message\` text NOT NULL,\
      \`DeviceReportedTime\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\
      \`ReceivedAt\` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\
      \`SyslogTag\` varchar(60) NOT NULL,\
      KEY \`FromHost\` (\`FromHost\`),\
      KEY \`SyslogTag\` (\`SyslogTag\`)\
    ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 row_format=COMPRESSED KEY_BLOCK_SIZE=4;"

Database Users

mysql -Be "DROP USER 'syslogwriter'@'localhost'; CREATE USER 'syslogwriter'@'localhost' IDENTIFIED BY 'secretpassword1';"
mysql -Be "GRANT INSERT ON Syslog.* To 'syslogwriter'@'localhost';"

mysql -Be "DROP USER 'syslogreader'@'localhost'; CREATE USER 'syslogreader'@'localhost' IDENTIFIED BY 'secretpassword2';"
mysql -Be "GRANT SELECT ON Syslog.* To 'syslogreader'@'localhost';"

mysql -Be "DROP USER 'syslogmaster'@'localhost'; CREATE USER 'syslogmaster'@'localhost' IDENTIFIED BY 'secretpassword3';"
mysql -Be "GRANT ALL ON Syslog.* To 'syslogmaster'@'localhost';"

mysql -Be "DROP DATABASE IF EXISTS loganalyzer; CREATE DATABASE loganalyzer;"
mysql -Be "DROP USER 'loganalyzer'@'localhost'; CREATE USER 'loganalyzer'@'localhost' IDENTIFIED BY 'secretpassword4';"
mysql -Be "GRANT ALL ON loganalyzer.* To 'loganalyzer'@'localhost';"
mysql -Be "FLUSH PRIVILEGES;"

Extend rsyslogd configuration for MySQL settings

echo "\$ModLoad ommysql" >> /etc/rsyslog.d/server.conf

Loganalyzer rsyslogd setup

echo "\$template php_log,\"insert into php_log (FromHost, Priority, Message, DeviceReportedTime, ReceivedAt, SyslogTag ) values ('%HOSTNAME%', \\" >> /etc/rsyslog.d/server.conf
echo "'%syslogpriority%', \\" >> /etc/rsyslog.d/server.conf
echo "'%msg%', \\" >> /etc/rsyslog.d/server.conf
echo "'%timereported:::date-mysql%', \\" >> /etc/rsyslog.d/server.conf
echo "'%timegenerated:::date-mysql%', \\" >> /etc/rsyslog.d/server.conf
echo "'%syslogtag%')\",SQL" >> /etc/rsyslog.d/server.conf
echo ":syslogtag, :omusrmsg:startswith, :omusrmsg:php :ommysql:localhost,Syslog,syslogwriter,secretpassword1;php_log stop" >> /etc/rsyslog.d/server.conf

Syslog filters

FQDN=`hostname -f`
echo "auth.*,kern.*,*.emerg,*.alert,*.crit,*.err,*.warning :ommysql:localhost,Syslog,syslogwriter,secretpassword1" >> /etc/rsyslog.d/server.conf
echo "if \$hostname != '$FQDN' then stop"  >> /etc/rsyslog.d/server.conf
chmod 640 /etc/rsyslog.d/server.conf

Restart rsyslogd

systemctl restart rsyslog

Install LogAnalyzer

Assuming Apache and PHP was already installed.

wget -P /usr/local/src/ http://download.adiscon.com/loganalyzer/loganalyzer-4.1.3.tar.gz
tar vxfz loganalyzer-*.tar.gz -C /usr/local/src
mv /usr/local/src/loganalyzer-*/src /var/www/html/loganalyzer
touch /var/www/html/loganalyzer/config.php
chown -Rv apache:apache /var/www/html/loganalyzer/config.php

Proceed with http://server/loganalyzer/

Mysql client profile setup

SQL credentials in .my.cnf

echo "[client]" > ~/.my.cnf
echo "password=secretpassword3" >> ~/.my.cnf
echo "port=3306" >> ~/.my.cnf
echo "user=syslogmaster" >> ~/.my.cnf
echo "socket=/var/lib/mysql/mysql.sock" >> ~/.my.cnf
echo "default-character-set=utf8" >> ~/.my.cnf

Script for cleaning logs

Keep logs for 20 days only but AUTH facility logs forever.

echo "mysql -Bs Syslog -e \"DELETE FROM Syslog.SystemEvents WHERE DeviceReportedTime <= NOW() - INTERVAL 20 DAY AND \\\`Facility\\\` NOT LIKE 'AUTH';\"" > /usr/local/bin/rsyslog_mysql_cleanup.sh
echo "mysql -Bs Syslog -e \"DELETE FROM Syslog.php_log WHERE DeviceReportedTime <= NOW() - INTERVAL 20 DAY;\"" >> /usr/local/bin/rsyslog_mysql_cleanup.sh
chmod 750 /usr/local/bin/rsyslog_mysql_cleanup.sh

Cronjob

Cleanup every 30 minutes.

echo "*/30 * * * * root /bin/sh /usr/local/bin/rsyslog_mysql_cleanup.sh" >> /etc/crontab
systemctl restart crond

Rsyslog clients setup

rsyslog clients in /etc/rsyslog.d/forwarding.conf:

$PreserveFQDN on
$ActionQueueFileName fwdRule1 # unique name prefix for spool files
$ActionQueueMaxDiskSpace 1g   # 1gb space limit (use as much as possible)
$ActionQueueSaveOnShutdown on # save messages to disk on shutdown
$ActionQueueType LinkedList   # run asynchronously
$ActionResumeRetryCount -1    # infinite retries if host is down
*.* @@rsyslog.loc.example.com:514
:syslogtag, :omusrmsg:startswith, :omusrmsg:php stop

PS! be sure to setup your own passwords