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 Agent 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:
|
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.
This mandatory directive specifies the ODBC data source connection string. |
Optional directives
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
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.
<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>
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.
<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>