Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Configure rsyslogd on CentOS 7 as Remote Syslog Server

Configure rsyslogd on CentOS 7 as Remote Syslog Server

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

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

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 and Tables

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);"

PHP Log

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

PHP Log

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

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

Configuration

Proceed with http://server/loganalyzer/

Cleanup old Logs

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

Create script

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

Add Cronjob

Cleanup every 30 minutes.

yum -y install crontabs
echo "*/30 * * * * root /bin/sh /usr/local/bin/rsyslog_mysql_cleanup.sh" >> /etc/crontab
systemctl enable crond
systemctl start crond

rsyslog Clients

On 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

TODO

  • TLS encrypted transport, maybe X509 authentication or general authentication?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.