HOWTO : Rsyslog + MySQL on FreeBSD

Pre-requisites

• you have ports in its default location
• you're running a FreeBSD build recent to 7.2
• You have a working install of MySQL, and are familiar with administering and securing it
• This is not a best practice but a quick-start guide

Install Rsyslog

1. Update your ports collection (see: FreeBSD Handbook: Using Ports)
2. Install Rsyslog and replace the stock syslogd, instructions are here.

Install MySQL output module

# cd /usr/ports/sysutils/rsyslog4-mysql
# make install clean

Create the database and tables

• Use the file with the SQL script to create the SQL layout:

mysql -u root -p < /usr/local/share/examples/rsyslog/mysql_createDB.sql

to read it in after you type the root mysql password

• Alternatively you can copy and paste it into the interactive mysql shell

CREATE DATABASE Syslog;
USE Syslog;
CREATE TABLE SystemEvents
(
        ID int unsigned not null auto_increment primary key,
        CustomerID bigint,
        ReceivedAt datetime NULL,
        DeviceReportedTime datetime NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        Message text,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL
);

CREATE TABLE SystemEventsProperties
(
        ID int unsigned not null auto_increment primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);

• Create the user that will have access to the specific database

  mysql> grant all privileges on Syslog.* to 'sysloguser'@'%' identified by 'syslogpass' with grant option

• Make sure to test the users ability to log in before you continue

Enable the module in Rsyslog

To enable the output module, edit the file /usr/local/etc/rsyslog.conf and add to the top:

$ModLoad ommysql # load MySQL functionality

Configure rsyslog to send specific logs to mysql in the rsyslog.conf configuration file. For example, if you want to log the Local0 facility to mysql, add this to the bottom of the configuration file:

local0.*       :ommysql:database-server,database-name,database-userid,database-password

Restart Rsyslog:

#/usr/local/etc/rc.d/rsyslogd restart