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 Agent is running under.

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 SQL datetime ID example in the User Guide.

Configuration

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

Required directives

The following directives are required for the module to start.

ConnectionString

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

SQL

This mandatory parameter sets the SQL statement the module will execute 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.

Optional directives

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.

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.

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.

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.

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 optional boolean directive instructs the module to only read logs that arrive after NXLog Agent is started. This directive comes into effect if a saved position is not found, for example on the first start, or when the SavePos directive is FALSE. When the SavePos directive is TRUE and a previously saved position is found, the module will always resume reading from the saved position. If ReadFromLast is TRUE, the MaxIDSQL directive must be set. If this directive is not specified, it defaults to FALSE.

The following matrix shows the outcome of this directive in conjunction with the SavePos directive:

ReadFromLast SavePos SavedPosition Outcome

TRUE

Reads events that are logged after NXLog Agent is started.

TRUE

Yes

Reads events from the saved position.

TRUE

FALSE

Reads events that are logged after NXLog Agent is started.

TRUE

FALSE

Yes

Reads events that are logged after NXLog Agent is started.

FALSE

TRUE

Reads all events.

FALSE

TRUE

Yes

Reads events from the saved position.

FALSE

Reads all events.

FALSE

Yes

Reads all events.

SavePos

If this boolean directive is set to TRUE, the last row id will be saved when NXLog Agent exits. The row id will be read from the cache file upon startup. The default is TRUE, the last row id will be saved if this directive is not specified. This directive affects the outcome of the ReadFromLast directive. The SavePos directive can be overridden by the global NoCache directive.

Creating and populating fields

When the im_odbc 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_odbc.

In addition to the field below, each column name returned in the result set is mapped directly to an NXLog Agent 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.

Examples

Example 1. Reading from an ODBC data source

This example uses im_odbc to connect to a database mydb and retrieve logs from logtable. Log records are written to file without any further processing.

nxlog.conf

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

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

Table 1. Input sample
RecordNumber	DateOccurred	Data
1234	2021-11-08 08:42:20	The service started successfully

Output sample

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

Example 2. Converting database records to JSON

This example uses im_odbc to connect to a database mydb and retrieve logs from logtable. 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 odbc>
    Module              im_odbc
    ConnectionString    DSN=mssql;database=mydb;
    SQL                 SELECT id, Timestamp AS EventTime,
                               LogLevel AS Severity, \
                               Message \
                        FROM logtable WHERE id > ?
    <Exec>
        if ($Severity == "DEBUG")
            drop();
        to_json();
    </Exec>
</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"
}

Example 3. 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

<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>