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
/etc/rsyslog.d/mysql.conf

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))
        )
Create table for Rsyslog

Share: