Rsyslog with MariaDB template
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)) )