NXLog User Guide

109.23. ODBC (im_odbc)

ODBC is a database independent abstraction layer for accessing databases. This module uses the ODBC API to read data from 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.

In order to continue reading only new log entries after a restart, the table must contain an auto increment, serial, or timestamp column named id in the returned result set. The value of this column is substituted into the ? contained in the SELECT (see the SQL directive).

Some data types are not supported by im_odbc. If a column of an unsupported type is included in the result set, im_odbc will log an unsupported odbc type error to the internal log. To read values from data types that are not directly supported, use the CAST() function to convert to a supported type. See the Reading Unsupported Types example below. Additionally, due to a change in the internal representation of datetime values in SQL Server, some timestamp values cannot be compared correctly (when used as the id) without an explicit casting in the WHERE clause. See the SQL Server Reading Logs by datetime ID example in the User Guide.

109.23.1. Configuration

The im_odbc module accepts the following directives in addition to the common module directives. The ConnectionString and SQL directives are required.

ConnectionString: This specifies the connection string containing the ODBC data source name.

SQL

This mandatory parameter sets the SQL statement the module will execute in order to query data from the data source. The select statement must contain a WHERE clause using the column aliased as id.

SELECT RecordNumber AS id, DateOccured AS EventTime, data AS Message
FROM logtable WHERE RecordNumber > ?

Note that WHERE RecordNumber > ? is crucial: without this clause the module will read logs in an endless loop. The result set returned by the select must contain this id column which is then stored and used for the next query.

IdIsTimestamp

When this directive is set to TRUE, it instructs the module to treat the id field as TIMESTAMP type. If this directive is not specified, it defaults to FALSE: the id field is treated as an INTEGER/NUMERIC type.

Warning

This configuration directive has been obsoleted in favor of IdType timestamp.

IdType

This directive specifies the type of the id field and accepts the following values: integer, timestamp, and uniqueidentifier. If this directive is not specified, it defaults to integer and the id field is treated as an INTEGER/NUMERIC type.

Note	The timestamp type in Microsoft SQL Server is not a real timestamp; see rowversion (Transact-SQL) on Microsoft Docs. To use an SQL Server timestamp type field as the id, set IdType to `integer`.

Note	The Microsoft SQL Server `uniqueidentifier` type is only sequential when initialized with the `NEWSEQUENTIALID` function. Even then, the IDs are not guaranteed to be sequential in all cases. For more information, see uniqueidentifier and NEWSEQUENTIALID on Microsoft Docs.

Note	The im_odbc module parses timestamps as local time, converted to UTC, and then saves them in the event record. This module does not apply any time offset for fields that include time zone information.

MaxIdSQL

This directive can be used to specify an SQL select statement for fetching the last record. MaxIdSQL is required if ReadFromLast is set to TRUE. The statement must alias the ID column as maxid and return at least one row with at least that column.

SELECT MAX(RecordNumber) AS maxid FROM logtable

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

ReadFromLast: This boolean directive instructs the module to only read logs that arrived after NXLog was started if the saved position could not be read (for example on first start). When SavePos is TRUE and a previously saved position value could be read, the module will resume reading from this saved position. If ReadFromLast is TRUE, the MaxIDSQL directive must be set. If this directive is not specified, it defaults to FALSE.

SavePos: This boolean directive specifies that the last row id should be saved when NXLog exits. The row id will be read from the cache file upon startup. The default is TRUE: the row id is saved if this directive is not specified. Even if SavePos is enabled, it can be explicitly turned off with the global NoCache directive.

109.23.2. Fields

The following fields are used by im_odbc.

In addition to the field below, each column name returned in the result set is mapped directly to an NXLog field name.

$raw_event (type: string)

This field is constructed from:

the EventTime column or the current time if EventTime was not returned in the result set;
the Hostname column or the hostname of the local system if Hostname was not returned in the result set;
the Severity column or INFO if Severity was not returned in the result set; and
all other columns as columnname: columnvalue, each starting on a new line.

109.23.3. Examples

Example 561. Reading from an ODBC Data Source

This example uses ODBC to connect to the mydb database and retrieve log messages. The messages are then forwarded to another agent in the NXLog binary format.

nxlog.conf


  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

  <Input odbc>
    Module              im_odbc
    ConnectionString    DSN=mssql;database=mydb;
    SQL                 SELECT RecordNumber AS id, \
                               DateOccured AS EventTime, \
                               data AS Message \
                        FROM logtable WHERE RecordNumber > ?
</Input>

<Output tcp>
    Module              om_tcp
    Host                192.168.1.1
    Port                514
    OutputType          Binary
</Output>

Example 562. Reading Unsupported Types

This example reads from an SQL Server database. The LogTime field uses the datetimeoffset type, which is not directly supported by im_odbc. The following configuration uses a SELECT statement that returns two columns for this field: EventTime for the timestamp and TZOffset for the time-zone offset value.

nxlog.conf


  1
2
3
4
5
6
7
8
9
10
11
12

  <Input mssql_datetimeoffset>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 17 for SQL Server}; Server=MSSQL-HOST; \
                        Trusted_Connection=yes; Database=TESTDB
    IdType  integer
    SQL     SELECT RecordID AS id, \
                   CAST(LogTime AS datetime2) AS EventTime, \
                   DATEPART(tz, LogTime) AS TZOffset, \
                   Message \
              FROM dbo.test1 WHERE RecordID > ?
    Exec    rename_field($id, $RecordID);
</Input>