DBI (om_dbi)

The om_dbi module allows NXLog to store log data in external databases. This module utilizes the libdbi database abstraction library, which supports various database engines such as MySQL, PostgreSQL, MSSQL, Sybase, Oracle, SQLite, and Firebird. An INSERT statement can be specified, which will be executed for each log, to insert into any table schema.

The im_dbi and om_dbi modules support GNU/Linux only because of the libdbi library. The im_odbc and om_odbc modules provide native database access on Windows.

libdbi needs drivers to access the database engines. These are in the libdbd-* packages on Debian and Ubuntu. CentOS 5.6 has a libdbi-drivers RPM package, but this package does not contain any driver binaries under /usr/lib64/dbd. The drivers for both MySQL and PostgreSQL are in libdbi-dbd-mysql. If these are not installed, NXLog will return a libdbi driver initialization error.

Configuration

The om_dbi module accepts the following directives in addition to the common module directives.

Driver: This mandatory directive specifies the name of the libdbi driver which will be used to connect to the database. A DRIVER name must be provided here for which a loadable driver module exists under the name libdbdDRIVER.so (usually under /usr/lib/dbd/). The MySQL driver is in the libdbdmysql.so file.

SQL: This directive should specify the INSERT statement to be executed for each log message. The field names (names beginning with $) will be replaced with the value they contain. String types will be quoted.

Option: This directive can be used to specify additional driver options such as connection parameters. The manual of the libdbi driver should contain the options available for use here.

Examples

These two examples are for the plain Syslog fields. Other fields generated by parsers, regular expression rules, the pm_pattern pattern matcher module, or input modules, can also be used. Notably, the im_msvistalog and im_mseventlog modules generate different fields than those shown in these examples.

Example 1. Storing Syslog in a PostgreSQL Database

Below is a table schema which can be used to store Syslog data:

CREATE TABLE log (
    id serial,
        timestamp timestamp  not null,
    hostname varchar(32) default NULL,
    facility varchar(10) default NULL,
    severity varchar(10) default NULL,
    application varchar(10) default NULL,
    message text,
    PRIMARY KEY (id)
);

The following configuration accepts log messages via TCP and uses libdbi to insert log messages into the database.

nxlog.conf

<Extension syslog>
    Module      xm_syslog
</Extension>

<Input tcp>
    Module      im_tcp
    ListenAddr  0.0.0.0:1234
    Exec        parse_syslog_bsd();
</Input>

<Output dbi>
    Module      om_dbi
    SQL         INSERT INTO log (facility, severity, hostname, timestamp, \
                             application, message) \
                VALUES ($SyslogFacility, $SyslogSeverity, $Hostname, '$EventTime', \
                    $SourceName, $Message)
    Driver      pgsql
    Option      host 127.0.0.1
    Option      username dbuser
    Option      password secret
    Option      dbname logdb
</Output>

<Route tcp_to_dbi>
    Path        tcp => dbi
</Route>

Example 2. Storing Logs in a MySQL Database

This configuration reads log messages from the socket and inserts them into a MySQL database.

nxlog.conf

<Extension syslog>
    Module      xm_syslog
</Extension>

<Input uds>
    Module      im_uds
    UDS         /dev/log
    Exec        parse_syslog_bsd();
</Input>

<Output dbi>
    Module      om_dbi
    SQL         INSERT INTO log (facility, severity, hostname, timestamp, \
                                 application, message) \
                VALUES ($SyslogFacility, $SyslogSeverity, $Hostname, '$EventTime', \
                        $SourceName, $Message)
    Driver      mysql
    Option      host 127.0.0.1
    Option      username mysql
    Option      password mysql
    Option      dbname logdb 
</Output>

<Route uds_to_dbi>
    Path        uds => dbi
</Route>