Deep Dive into Zabbix Database Monitoring with ODBC

Good morning everyone, and welcome back to my channel, Quadrata! I’m Dimitri Bellini, and this week, we’re diving deep into Zabbix again. Why? Because many of you in our fantastic Zabbix Italia Telegram channel asked for a closer look at database monitoring with Zabbix.

Zabbix offers a few ways to monitor databases – using Zabbix Agent 2, User Parameters, and ODBC. While Agent 2 is great for standard metrics and User Parameters offer script-based flexibility (which I personally enjoy!), today we’re focusing on what I consider the most elegant and powerful method for many scenarios: **ODBC (Open Database Connectivity)**.

What is ODBC and Why Use It with Zabbix?

ODBC is a standard API (Application Programming Interface) born years ago, designed to allow applications to communicate with various databases without needing to know the specifics of each database system. Think of it as a universal translator for databases.

Here’s the basic idea:

• Your application (in our case, Zabbix Server or Proxy) talks to the ODBC layer using standard SQL commands.
• The ODBC layer uses specific **drivers** for each database type (MySQL, Oracle, Postgres, etc.).
• These drivers handle the native communication with the target database.

This decoupling means Zabbix doesn’t need built-in drivers for every database, making it more flexible. On Linux systems, the common implementation is UnixODBC.

Setting Up ODBC for Zabbix

Installation (Linux Example)

Getting UnixODBC installed is usually straightforward as it’s often in standard repositories. For Red Hat-based systems (like Rocky Linux, AlmaLinux), you’d typically run:

dnf install unixodbc unixodbc-devel

(The `devel` package might not always be necessary, but can be helpful).

After installing UnixODBC, you need the specific ODBC driver for the database you want to monitor.

Crucial Driver Note for MySQL/MariaDB

dnf install MariaDB-connector-odbc

Configuration Files: odbcinst.ini and odbc.ini

UnixODBC typically uses two main configuration files:

• odbcinst.ini: Defines the available drivers, giving them an alias and pointing to the driver library file.
• odbc.ini: Defines Data Source Names (DSNs). A DSN is a pre-configured connection profile containing the driver alias, server address, port, user, password, and database name.

Two Approaches to Connection: DSN vs. Connection String

1. The DSN Method (Using odbc.ini)

You can define all your connections in odbc.ini and then simply refer to the DSN name in Zabbix. You can test this setup from the command line using the isql utility:

isql your_dsn_name

If it connects successfully, you’ll get a prompt, confirming your ODBC setup works.

However, I personally find this method less flexible for Zabbix. Managing static entries in odbc.ini for potentially hundreds of databases can become cumbersome.

2. The Connection String Method (My Preferred Way!)

Instead of relying on odbc.ini for connection details, you can provide all the necessary information directly within Zabbix using a **connection string**. This bypasses the need for DSN entries in odbc.ini (though odbcinst.ini is still needed to define the driver itself).

You can test this approach from the command line too:

isql -k "Driver=YourDriverAlias;Server=your_db_host;Port=3306;User=your_user;Password=your_password;Database=your_db;"

(Replace the placeholders with your actual details. The exact parameters might vary slightly depending on the driver).

This method offers much greater flexibility, especially when combined with Zabbix templates and macros, as we’ll see.

Configuring Zabbix for ODBC Monitoring

Creating the Zabbix Item

To monitor a database using ODBC in Zabbix:

1. Create a new item for your host.
2. Set the **Type** to Database monitor.
3. The **Key** format is crucial: db.odbc.select[,].

   • : A unique name for this specific check within the host (e.g., `mysql_version`, `user_count`). This ensures the key is unique.
   • : Here you either put your DSN name (if using odbc.ini) OR the full connection string (if bypassing odbc.ini).

4. If using the connection string method, you leave the DSN part empty but include the connection string within the key’s parameters, often enclosed in quotes if needed, or directly if simple. *Correction from video explanation: The Zabbix key structure is slightly different. It’s `db.odbc.select[unique_name,dsn]` or `db.odbc.select[unique_name,,connection_string]`. Notice the double comma when omitting the DSN.*
5. The most important field is the **SQL query** field. This is where you put the actual SQL query you want Zabbix to execute.
6. You can optionally provide Username and Password in the dedicated fields, which might override or complement details in the DSN/connection string depending on the driver and configuration.
7. Set the **Type of information** based on the expected query result (Numeric, Text, etc.).

Example with Connection String

Here’s how an item key might look using the connection string method (notice the empty DSN parameter indicated by the double comma):

db.odbc.select[CountZabbixTables,, "Driver=MariaDB;Server=127.0.0.1;Port=3306;Database=zabbix;User={$ZABBIX_DB_USER};Password={$ZABBIX_DB_PASSWORD};"]

And in the **SQL query** field for this item, I might put:

SELECT count(*) FROM information_schema.tables WHERE table_schema = 'zabbix';

Leveraging User Macros for Flexibility

The real power of the connection string method shines when you use Zabbix User Macros (like {$ZABBIX_DB_USER}, {$ZABBIX_DB_PASSWORD}, {$DB_HOST}, {$DB_NAME}) within the string. This allows you to create generic templates and customize the connection details per host via macros – incredibly useful for large or complex environments!

ODBC for Custom Queries vs. Agent 2 for System Health

It’s important to understand the typical use cases:

• Standard Zabbix Templates (Agent 2 or ODBC): These usually focus on monitoring the *health and performance* of the database system itself (e.g., queries per second, buffer usage, connection counts, uptime).
• Manual ODBC Items (like we’re discussing): This method is **perfect for running custom SQL queries**. Need to check the number of rows in a specific table? Verify if a critical configuration value exists? Confirm application data is being populated correctly? ODBC monitoring configured this way is your go-to solution.

While you *could* potentially use Agent 2 with User Parameters and scripts for custom queries, ODBC often provides a cleaner, more centralized, and integrated way to achieve this directly from the Zabbix Server or Proxy.

Putting It All Together: A Practical Example

In the video, I demonstrated creating an item to count the number of tables in my Zabbix database using the connection string method. The key steps were:

1. Define the item with Type `Database monitor`.
2. Construct the key using `db.odbc.select` and the connection string.
3. Enter the `SELECT count(*)…` query in the SQL query field.
4. Use the fantastic **Test** button in Zabbix! This lets you immediately check if the connection works and the query returns the expected data (in my case, 173 tables) without waiting for the item’s update interval.

This confirms the connection from Zabbix to the database via ODBC is working correctly.

Troubleshooting Tips

If things don’t work right away (which happens!), follow these steps:

• Test Connectivity First: Always use the `isql` command-line tool (either with the DSN or the `-k “connection_string”`) to verify basic ODBC connectivity *before* configuring Zabbix.
• Check Logs: Examine the Zabbix Server or Zabbix Proxy logs for detailed error messages related to database monitors.
• Consult Documentation: The official Zabbix documentation has a dedicated section on ODBC monitoring.
• Verify Driver Path: Ensure the driver path specified in `odbcinst.ini` is correct.
• Permissions: Make sure the database user configured for Zabbix has the necessary permissions to connect and execute the query.
• Take a Breath: Rushing leads to mistakes. Double-check configurations, read errors carefully, and approach it methodically.

Conclusion and Next Steps

ODBC monitoring in Zabbix is a highly flexible and powerful tool, especially when you need to go beyond standard system metrics and execute custom SQL queries to validate data or check specific application states. While the initial setup requires careful attention to drivers and connection details, the connection string method combined with user macros offers excellent scalability.

Thanks again for watching and reading. Happy monitoring!

Ciao,
Dimitri Bellini