DBI (im_dbi)

The im_dbi module allows NXLog Agent to pull log data from external databases. This module utilizes the libdbi database abstraction library, which supports various database engines such as MySQL, PostgreSQL, Oracle, SQLite, and Firebird. A SELECT statement can be specified, which will be executed periodically to check for new records.

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 Agent will return a libdbi driver initialization error.

Configuration

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

Required directives

The following directives are required for the module to start.

Driver

This mandatory directive specifies the name of the libdbi driver that 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 SELECT statement to be executed every PollInterval seconds. The module automatically appends a WHERE id > ? LIMIT 10 clause to the statement. The result set returned by the SELECT statement must contain an id column which is then stored and used for the next query.

Optional directives

Option

This directive can be used to specify additional connection options. Refer to the libdbi driver documentation of your database engine for supported connection options. The module works in a failover configuration if you specify the host option multiple times. If the first host is unreachable, the module automatically fails over to the next one. If the last host is unreachable, the module fails over to the first host.

PollInterval

This directive specifies how frequently the module will check for new records, in seconds. If this directive is not specified, the default is 1 second. Fractional seconds may be specified (PollInterval 0.5 will check twice every second).

SavePos

If this boolean directive is set to TRUE, the position will be saved when NXLog Agent exits. The position will be read from the cache file upon startup. The default is TRUE: the position will be saved if this directive is not specified. Even if SavePos is enabled, it can be explicitly turned off with the global NoCache directive.

Creating and populating fields

When the im_dbi module reads a record from a database, it creates and populates the fields returned by the SQL statement. The fields can be used for further processing or to convert the log record to a different output format, such as JSON or XML. Additionally, the $raw_event core field is populated as follows:

EventTime Hostname Severity field1=value1 field2=value2

If the EventTime, Hostname, and Severity fields are not returned as part of the database record, the following default values are used:

EventTime: The value of the $EventReceivedTime core field.
Hostname: The value returned by the hostname() function.
Severity: INFO

The configuration Examples below demonstrate how database log records can be processed with NXLog Agent.

Fields

The following fields are used by im_dbi.

$raw_event (type: string): A list of event fields in key-value pairs.

Examples

Example 1. Reading from a MySQL database

This example uses im_dbi and the MySQL driver to connect to the logdb database and retrieve logs from table log. Log records are written to file without any further processing.

nxlog.conf

<Input dbi>
    Module    im_dbi
    Driver    mysql
    Option    host 127.0.0.1
    Option    username mysql
    Option    password mysql
    Option    dbname logdb
    SQL       SELECT id, timestamp AS EventTime, message \
              FROM log
</Input>

<Output file>
    Module    om_file
    File      '/path/to/output/file'
</Output>

Table 1. Input sample
id	timestamp	message
1234	2021-11-08T08:42:20	The service started successfully

Output sample

2021-11-08 08:42:20 SERVER-01 INFO message="The service started successfully"

Example 2. Converting database records to JSON

This example uses im_dbi to connect to a database logdb and retrieve logs from table log. If the log record has a severity equal to DEBUG, it is dropped. Otherwise, the log record is converted to JSON using the to_json() procedure of the xm_json module.

nxlog.conf

<Extension json>
    Module    xm_json
</Extension>

<Input dbi>
    Module    im_dbi
    Driver    mysql
    Option    host 127.0.0.1
    Option    username mysql
    Option    password mysql
    Option    dbname logdb
    SQL       SELECT id, Timestamp AS EventTime, \
              LogLevel AS Severity, Message \
              FROM log
    Exec      to_json();
</Input>

Table 2. Input sample
id	Timestamp	LogLevel	Message
1234	2021-11-08T09:12:05	INFO	The service started successfully

Output sample in JSON format

{
  "id": 1234,
  "EventTime": "2021-11-08T09:12:05.000000+01:00",
  "Severity": "INFO",
  "Message": "The service started successfully",
  "EventReceivedTime": "2021-11-08T09:13:16.332925+01:00",
  "SourceModuleName": "odbc",
  "SourceModuleType": "im_odbc"
}