ODBC (om_odbc)

ODBC is a database independent abstraction layer for accessing databases. This module uses the ODBC API to write data to database tables. There are several ODBC implementations available, and this module has been tested with unixODBC on Linux (available in most major distributions) and Microsoft ODBC on Windows.

Setting up the ODBC data source is not in the scope of this document. Please consult the relevant ODBC guide: the unixODBC documentation or the Microsoft ODBC Data Source Administrator guide. The data source must be accessible by the user NXLog is running under.

The "SQL Server" ODBC driver is unsupported and does not work. Instead, use the "SQL Server Native Client" or the "ODBC Driver for SQL Server" to insert records into a Microsoft SQL Server database.

In addition to the SQL directive, this module provides two functions, sql_exec() and sql_fetch(), which can be executed using the Exec directive. This allows more complex processing rules to be used and also makes it possible to insert records into more than one table.

Both sql_exec() and sql_fetch() can take bind parameters as function arguments. It is recommended to use bind parameters instead of concatenating the SQL statement with the value. For example, these two are equivalent but the first is dangerous due to the lack of escaping:

$retval = sql_exec("INSERT INTO log (id) VALUES (" + $id + ")");

$retval = sql_exec("INSERT INTO log (id) VALUES (?)", $id);

Configuration

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

Required directives

The following directives are required for the module to start.

ConnectionString

This mandatory directive specifies the ODBC data source connection string.

Optional directives

SQL

This optional directive can be used to specify the INSERT statement to be executed for each log message. If the statement fails for an event, it will be attempted again. If the SQL directive is not given, then an Exec directive should be used to execute the sql_exec() function.

Functions

The following functions are exported by om_odbc.

string sql_error(): Return the error message from the last failed ODBC operation.

boolean sql_exec(string statement, varargs args): Execute the SQL statement. Bind parameters should be passed separately after the statement string. Returns FALSE if the SQL execution failed: sql_error() can be used to retrieve the error message.

boolean sql_fetch(string statement, varargs args): Fetch the first row of the result set specified with a SELECT query in statement. The function will create or populate fields named after the columns in the result set. Bind parameters should be passed separately after the statement string. Returns FALSE if the SQL execution failed: sql_error() can be used to retrieve the error message.

Examples

Example 1. Write Events to SQL Server

This configuration uses a DSN-less connection and SQL Authentication to connect to an SQL Server database. Records are inserted into the dbo.test1 table’s timestamp and message columns, using the $EventTime and $Message fields from the current event.

nxlog.conf

<Output mssql>
    Module              om_odbc
    ConnectionString    Driver={ODBC Driver 13 for SQL Server}; Server=MSSQL-HOST; \
                        UID=test; PWD=testpass; Database=TESTDB
    SQL                 "INSERT INTO dbo.test1 (timestamp, message) VALUES (?,?)", \
                        $EventTime, $Message
</Output>

Example 2. Complex Write to an ODBC Data Source

In this example, the events read from the TCP input are inserted into the message column. The table has an auto_increment id column, which is used to fetch and print the newly inserted line.

nxlog.conf

<Input tcp>
    Module              im_tcp
    ListenAddr          0.0.0.0:1234
</Input>

<Output odbc>
    Module              om_odbc
    ConnectionString    DSN=mysql_ds;username=mysql;password=mysql;database=logdb;
    <Exec>
        if ( sql_exec("INSERT INTO log (facility, severity, hostname, timestamp, " +
                      "application, message) VALUES (?, ?, ?, ?, ?, ?)",
                      1, 2, "host", now(), "app", $raw_event) == TRUE )
        {
            if ( sql_fetch("SELECT max(id) as id from log") == TRUE )
            {
                log_info("ID: " + $id);
                if ( sql_fetch("SELECT message from log WHERE id=?", $id) == TRUE )
                {
                    log_info($message);
                }
            }
        }
    </Exec>
</Output>

<Route tcp_to_odbc>
    Path                tcp => odbc
</Route>