Skip to content

Collect logs from SQL database using ODBC

Introduction

The recommended way of extracting logs and other events from databases is to use ODBC. ODBC provides a unified way how to connect TeskaLabs LogMan.io to various database systems.

ODBC drivers

TeskaLabs LogMan.io collector container image is shipped with following ODBC drivers:

  • ODBC Driver 18 for SQL Server (Microsoft SQL Server)
  • ODBC Driver 17 for SQL Server (Microsoft SQL Server)
  • PostgreSQL, Unicode and ANSI)
  • FreeTDS for Microsoft SQL Server and Sybase databases.
  • MariaDB also works for MySQL
  • Oracle (works for all recent Oracle Database versions)
  • Firebird 2

ODBC drivers for other databases can be easily added to TeskaLabs LogMan.io collector. The relevant ODBC driver must be compatible with Debian GNU/Linux 12 (bookworm), x86-64. The collector uses unixODBC for managing ODBC drivers.

The ODBC configuration is done in /etc/odbcinst.ini file, present in the TeskaLabs LogMan.io collector container.

Content of pre-created /etc/odbcinst.ini file 
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.5.so.1.1
UsageCount=1

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[FreeTDS]
Description=TDS driver (Sybase/MS SQL)
Driver=libtdsodbc.so
Setup=libtdsS.so
CPTimeout=
CPReuse=
UsageCount=1

[MariaDB Unicode]
Driver=libmaodbc.so
Description=MariaDB Connector/ODBC(Unicode)
Threading=0
UsageCount=1

[Oracle 19]
Description=Oracle ODBC driver for Oracle 19
Driver=/usr/lib/oracle/19.19/client64/lib/libsqora.so.19.1
Setup=/usr/lib/oracle/19.19/client64/lib/libsqora.so.19.1
UsageCount=1

[Firebird 2]
Description=Firebird 2 (for version 2.5 and older) ODBC driver
Driver=libOdbcFb.so
Setup=libOdbcFb.so
UsageCount=1

Collector input configuration

The input source specification is input:ODBC:.

Example of the ODBC collector configuration:

input:ODBC:ODBCInput:
  dsn: Driver={FreeTDS};Server=MyServer;Port=1433;Database=MyDatabase;TDS_Version=7.3;UID=MyUser;PWD=MyPassword
  query: SELECT * FROM mytable WHERE {increment_where_clause} ORDER BY insertion_time;
  increment_strategy: date
  increment_first_value: "2020-10-01 00:00:00.000"
  increment_column_name: "insertion_time"
  chilldown_period: 30
  last_value_storage: /data/var/last_value_storage
  output: smart

Note

You can specify multiple input:ODBC: section (input:ODBC:MyDatabase01:, input:ODBC:MyDatabase02: and so on) to configure more than one ODBC extraction. Alternatively, you can deploy new collector container, dedicated to each ODBC source.

DSN

DSN (Data Source Name) is used to describe a connection to a data source. The DSN term overlaps with "ODBC connection string".

Example of DSN:

Driver={FreeTDS};Server=MyServer;Port=1433;Database=MyDatabase;UID=MyUser;PWD=MyPassword

The TeskaLabs LogMan.io recommends the DSN to include a Driver specification.

List of available drivers can be extracted by odbcinst command:

$ docker run -it lmio-collector odbcinst -q -d
[ODBC Driver 18 for SQL Server]
[ODBC Driver 17 for SQL Server]
[PostgreSQL Unicode]
[FreeTDS]
[MariaDB]
[Oracle 19]
[Firebird 2]

Oracle DSN

DRIVER={Oracle 19};Dbq=//<host>:<port>/<SID>;UID=<user>;PWD=<password>;

PostgreSQL DSN

Driver={PostgreSQL Unicode};Server=<host>;Port=<port>;Database=<database>;Uid=<user>;Pwd=<password>;

Tip

More examples of ODBC DSNs can be found here.

Troubleshooting

Investigate a database structure

The container of TeskaLabs LogMan.io collector provides a simple command-line tool for executing SQL queries over ODBC. This tool can be used to validate the connectivity to the target database and to investigate a structure of the database.

$ docker run -it lmio-collector odbccli.py "DRIVER={Oracle};..."
TeskaLabs LogMan.io ODBC CLI - Interactive Mode
Connecting to database ...
Success!

Enter SQL SELECT queries (type 'quit' or 'exit' to exit):

SQL>  SELECT * from dual;

Results (1 row):
+---------+
| DUMMY   |
+=========+
| X       |
+---------+

Add a ODBC Trace

If you need greater insight into the ODBC connectivity, you can enable ODBC tracing.

Add this section to the /etc/odbcinst.ini :

[ODBC]
Trace = yes
TraceFile = /tmp/trace.log

When the collector is started, the trace output of the ODBC system is stored in the file /tmp/trace.log. This file is available also outside of the container.

Verify the ODBC configuration

The command odbcinst -j (launched within the container) can be used to verify ODBC readiness:

$ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8