Rsyslog with MariaDB template

2017-08-27 / Published in blog / [stefan]

For one of my web application (which is a syslog web frontend) I am logging syslog messages into a MariaDB database. I am not using the default Rsyslog template, I am using a different database and tables so I had to write my own template. My mysql.conf for rsyslog looks like:


$ModLoad ommysql

$template MariaDbTpl, "INSERT INTO Log_Messages \
(receivedAt, deviceTime, facility, priority, fromHost, syslogTag, message) \ 
VALUES ('%timegenerated:::date-mysql%', '%timereported:::date-mysql%', \
%syslogfacility%, %syslogpriority%, '%HOSTNAME%', '%syslogtag%', '%msg%')",SQL

*.* :ommysql:localhost,devel_opennims,***USERNAME***,***PASSWORD***;MariaDbTpl

For my web application I created the Log_Messages table like shown in the SQL query below. Also note I'm using a fulltext index for the log message.


CREATE TABLE `Log_Messages` (
    `aid` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `receivedAt` DATETIME NOT NULL,
    `deviceTime` DATETIME NOT NULL,
    `facility` SMALLINT(6) NULL DEFAULT NULL,
    `priority` SMALLINT(6) NULL DEFAULT NULL,
    `fromHost` VARCHAR(64) NULL DEFAULT NULL,
    `syslogTag` VARCHAR(64) NULL DEFAULT NULL,
    `message` TEXT NULL,
    PRIMARY KEY (`aid`, `receivedAt`),
    INDEX `IDX_Log_FromHost` (`fromHost`),
    INDEX `IDX_Log_Facility` (`facility`),
    INDEX `IDX_Log_ReceivedAt` (`receivedAt`),
    INDEX `IDX_Log_Priority` (`priority`),
    INDEX `IDX_Log_Fulltext` (`message`(100))
)