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.
This specifies the connection string containing the ODBC data source name. |
|
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.
Note that |
Optional directives
When this directive is set to
|
|||||||||||||||||||||||||||||||||||||
This directive specifies the type of the id field and accepts the following values:
|
|||||||||||||||||||||||||||||||||||||
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.
|
|||||||||||||||||||||||||||||||||||||
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 ( |
|||||||||||||||||||||||||||||||||||||
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 The following matrix shows the outcome of this directive in conjunction with the SavePos directive:
|
|||||||||||||||||||||||||||||||||||||
If this boolean directive is set to |
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 ifEventTime
was not returned in the result set; -
the
Hostname
column or the hostname of the local system ifHostname
was not returned in the result set; -
the
Severity
column orINFO
ifSeverity
was not returned in the result set; and -
all other columns as
columnname: columnvalue
, each starting on a new line.
-
Examples
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.
<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>
RecordNumber | DateOccurred | Data |
---|---|---|
1234 |
2021-11-08 08:42:20 |
The service started successfully |
2021-11-08 08:42:20 SERVER-01 INFO id="1234" Message="The service started successfully"
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.
<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>
id | Timestamp | LogLevel | Message |
---|---|---|---|
1234 |
2021-11-08T09:12:05 |
INFO |
The service started successfully |
{
"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"
}
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.
<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>