Collect logs from 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 LogMan.io to various database systems.

Tip

Examples of ODBC connection strings can be found here.

ODBC driver and configuration¶

You need to provide the ODBC driver for a database system you want to integrate with LogMan.io. The relevant ODBC driver must be compatible with Ubuntu 20.04 LTS, 64bit.

ODBC drivers needs to be deployed into the LogMan.io Collector, specifically into /odbc directory. Alternatively, our support will help you to deploy a correct ODBC driver for your database system or provide LogMan.io Collector with bundled ODBC driver.

Note

ODBC drivers are exposed to LogMan.io collector software via Docker volumes.

The ODBC configuration is done in /odbc/odbcinst.ini and odbc.ini files.

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 viewAlerts WHERE {increment_where_clause} ORDER BY Cas Time;
  increment_strategy: date
  increment_first_value: "2020-10-01 00:00:00.000"
  increment_column_name: "Time"
  chilldown_period: 30
  output: WebSocket
  last_value_storage: /data/var/last_value_storage

MySQL ODBC configuration¶

MySQL ODBC drivers can be obtained on the following link. The driver package needs to be extracted into /odbc/mysql directory.

Entries in the /odbc/odbcinst.ini:

[MySQL ODBC 8.0 Unicode Driver]
Driver=/odbc/mysql/libmyodbc8w.so
UsageCount=1

[MySQL ODBC 8.0 ANSI Driver]
Driver=/odbc/mysql/libmyodbc8a.so
UsageCount=1

Microsoft SQL Server ODBC configuration¶

Microsoft SQL Server ODBC drivers can be obtained on the following link.

Entries in the /odbc/odbcinst.ini:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/odbc/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
UsageCount=1

Example of the connection string:

Driver={ODBC Driver 17 for SQL Server};Server=<server_name>;Authentication=ActiveDirectoryPassword;UID=<username>;PWD=<password>;Database=<database>;TrustServerCertificate=Yes

Microsoft SQL Server ODBC alternative configuration¶

Alternative connectivity to Microsoft SQL Server is provided by FreeTDS project, respective its ODBC driver.

Entries in the /odbc/odbcinst.ini:

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/odbc/freetds/libtdsodbc.so
Setup=/odbc/freetds/libtdsodbc.so
UsageCount=1

Example of the connection string:

Driver={FreeTDS};Server=<server_name>;Port=<server_port>;Database=<database>;UID=<username>;PWD=<password>;TDS_Version=7.3

MariaDB ODBC configuration¶

MariaDB ODBC drivers can be obtained on the following link.

SAP IQ, Sybase IQ, Sybase ASE ODBC configuration¶

SAP IQ / Sybase IQ / Sybase ASE / SQL Anywhere ODBC drivers needs to be downloaded from SAP support page. The driver package needs to be extracted into /odbc/sybase directory.

Entry in the /odbc/odbcinst.ini:

[ODBC Driver for Sybase IQ]
Description=Sybase IQ
Driver=/odbc/sybase/IQ-16_1/lib64/libdbodbc17.so
UsageCount=1

Example of the connection string:

Driver={ODBC Driver for Sybase IQ};UID=<username>;PWD=<password>;Server=<server_name>;DBN=<database_name>;CommLinks=TCPIP{host=<host>;port=<port>};DriverUnicodeType=1

Troubleshooting¶

Add a ODBC Trace¶

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

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

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

Then the collector is started, the trace output of the ODBC system is stored in the file /odbc/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

Docker-compose¶

The LogMan.io collector can be started using docker-compose.

This is an extract of relevant entries from docker-compose.yaml:

lmio-collector:
  image: docker.teskalabs.com/lmio/lmio-collector

  ...

  volumes:
    - /odbc/odbcinst.ini:/etc/odbcinst.ini
    - /odbc/odbc.ini:/etc/odbc.ini
    - /odbc:/odbc

  ...

  network_mode: host

Afterwards, the LogMan.io Collector needs to be recreated with:

docker-compose up -d