Zabbix Database Monitor English

Sorry for my bad english! This article is available in German language too. Zabbix_Database_Monitor

Introduction

Zabbix offers the possibility to query any databases with SQL queries, save the results as an item-value and to allow further processing with triggers. The ability to query databases is useful in many applications.

You can for example monitor how many users are logged in on your webportal. If you use a syslog daemon with SQL backend, Zabbix can then look in a very flexible and effective way for log messages in the database. If you want incoming mail to be analyzed from Zabbix, you can do it easily. The open-source software dbmail writes all incoming mail into an SQL database. With the Zabbix Database Monitor you are able to monitor, for example, when the last message was received from your backup software and whether it contains the expected keywords that show you that the backup was successfully created.

To query a database Zabbix uses the Open Database Connectivity (ODBC), a concept invented by Microsoft. ODBC is a standardized database interface between applications (Zabbix) and any database. Zabbix should communicate only with the ODBC API and the database is not connected directly. This has the advantage that Zabbix does not need drivers for each database. Only the ODBC drivers need to be set up.

There are two open source versions of ODBC. UnixODBC and iODBC. Zabbix can use both. In order to query databases via ODBC the Zabbix server and possibly also the proxies need to be compiled with the ODBC option (--with-odbc). If your original installation of Zabbix was compiled without ODBC you must recompile the Zabbix server. You should do this with the the same sources with which you have compiled the running Zabbix server the first time.

install UnixODBC

Compiling the sources

Before you can use the ODBC option in Zabbix, you must first install UnixODBC. All distributions offer packages for UnixODBC, but these are usually too old. Also in Debian, some header files are missing in the package unixodbc-dev. Save yourself time and trouble and install UnixODBC directly from the current sources.

Download the sources from the Internet. Unpack the tarball. With the usual commands you can compile and install UnixODBC.

cd / usr / src
wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz
tar zxvf unixODBC-2.3.0.tar.gz
cd unixODBC-2.3.0
./configure --prefix=/usr --sysconfdir=/etc
make
make install

On 64bit systems you should specify the following path where the libraries are installed.

./configure --prefix=/usr --sysconfdir=/etc --libdir=/usr/lib64

With the above commands UnixODBC is installed in the default directories on your Linux system. The libraries go to /usr/lib or /usr/lib64 and the executables are located in /usr/bin/ . If you use a different prefix, you may need to adjust the "Library Path".

Installing database drivers

ODBC must know how to talk to a specific database. A driver is needed for each database type. These drivers are normally provided by the vendors as a free download.

MySQL ODBC driver

Download the ODBC drivers for Linux from the website of MySQL Also, SUSE or Red Hat users should download the general ODBC driver as a tar.gz file. Since we did not install UnixODBC via the package manager, installing the driver via RPM will fail because of unresolved dependencies. Unpack the file after the download and copy the drivers (*.so files) into your system.

cd /usr/src
wget http://ftp.gwdg.de/pub/misc/mysql/Downloads/Connector-ODBC/5.1/mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-32bit.tar.gz
tar xzf mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-32bit.tar.gz
mkdir /usr/lib/odbc/
cp /usr/src/mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-32bit/lib/* /usr/lib/odbc/

On a 64bit system you are using of course the 64bit version of the driver

wget http://ftp.gwdg.de/pub/misc/mysql/Downloads/Connector-ODBC/5.1/mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz
tar xzf mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz
mkdir /usr/lib64/odbc/
cp /usr/src/mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit/lib/* /usr/lib64/odbc/

Now check whether all the other libraries that are required by the MySQL ODBC driver are available on your system.

ldd /usr/lib/odbc/libmyodbc5.so   #32bit system
ldd /usr/lib64/odbc/libmyodbc5.so #64bit system

If libraries are marked with "Not found", you must install them via the package manager.

If ldd shows that all libraries are available, you can announce the MySQL ODBC driver to ODBC. Create the file /etc/odbcinst.ini with the following content.

[MySQL5]
Description = MySQL5
Driver = / usr/lib/odbc/libmyodbc5.so

or

[MySQL5]
Description = MySQL5
Driver = / usr/lib64/odbc/libmyodbc5.so

Setting up a DSN

If an application wants to connect to a database via ODBC, a DSN is used. A DSN must exist for each database that Zabbix or any other application wants to connect to via ODBC. The DSN determines which driver is used for the connection. In addition, further connection details like IP, user name, database name, etc. are stored in the DSN configuration.

Create a file /etc/odbc.ini with the following contents:

[mysql-test]
Driver = MySQL5
DATABASE = zabbix
PWD = 12345
SERVER = 127.0.0.1
PORT = 3306
UID = root

The value in brackets is the name of the DSN, as it has to be used from within the application (Zabbix). The driver has to be specified exactly as it was inserted into the file /etc/odbcinst.ini. You don´t need to enter Username and password into the DSN. These can be sent by the application too.

Testing UnixODBC

Before you can integrate ODBC into Zabbix you should test if it works. If the ini-files were created, you can use the command

isql <DSN> <user> <password>

to test a DSN. If the user name and password were already entered in the odbc.ini, it is sufficient to give the DSN. e.g.

isql mysql-test

If everything is set up properly, you should be logged on to the database by console. Enter a SQL query. If the connection fails, try

isql -v <DSN>

Compile Zabbix with UnixODBC

If you can connect successfully with UnixODBC the Zabbix server can be recompiled. If your Zabbix server is already running, you should just compile a new binary zabbix_sever, which you exchange for the current one. So do not call the command "make install". This would copy many files into your system and possibly overwrite files unintentionally.

Change to the directory with the Zabbix source code and enter "./configure" with just the options that you have previously used for compiling Zabbix. Now add the additional option --with-unixodbc Example:

./configure --enable-server --enable-agent --with-mysql --with-net-snmp --with-openipmi --with-unixodbc --prefix=/opt/zabbix 

checking for odbc_config... /usr/local/bin/odbc_config
checking for main in -lodbc... yes
checking whether unixodbc is usable... yes

If the configure-command confirmed that unixodbc was found and used, you can compile the server binary with make and then copy the file to your directory, where zabbix_server is installed. Make a backup before you overwrite the old file.

When you start the Zabbix server, the server should log the entry "ODBC = yes". You can now query databases via ODBC.

Set up Items with type Database Monitor

A database query is set up as an item in the Zabbix server. The result of the database query is then stored as the item value. Insert a new item and select the type "Database Monitor".

You can not select the key from a menu or a wizard. Fill in the following as key:

db.odbc.select[what_you_like]

The key must always start with db.odbc.select. In square brackets, enter an arbitrary string, which you use to identify the item in further configurations i.e. for configuring triggers.

In the field "Additional parameters" enter the DSN as the first line. This DSN must exist in the /etc/odbc.ini. If username and password are not stored in the DSN, fill them in below the DSN. In the last line, you specify the SQL command to be run. E.g.

DSN = zabbix
sql = select count (*) from items;

Or

DSN = zabbix
USER = zabbix
PWD = secret
sql = select count (*) from items;

Item db monitor.png

Requirements for the SQL queries

Note:

  • The SQL command must begin with a lowercase sql=
  • The SQL command must begin with select
  • The SQL command mustn´t contain any line breaks.
  • The query must return one value only.
  • If the query returns more than one column, only the first column is read.
  • If a query returns more than one line, only the first line is read.
  • Queries may but need not be terminated with a semicolon.
  • Global macros e.g. $ {HOSTNAME} will not be replaced.

Look at each item for the correct type of information. If the database query returns an integer, select Numeric Unsigned. If you expect text as returning value, select text as type of information. And so on.

You can create items of type Database Monitor for any host. The database query is always executed by the Zabbix server. If a host is monitored by a proxy, then the interrogation of the database is executed by the proxy. This assumes that you have installed UnixODBC on the proxies and that the proxy is compiled with the option --with-unixodbc. The configuration of the DSN has to be present on the proxies as well.

Optimize Database Monitor

The requirement, that the SQL query can only be entered without line breaks is often a problem. Longer queries can be formatted in a readable fashion only with line breaks.

Because the source code of Zabbix is open, you can patch the Zabbix server to allow linebreaks in the SQL commands.

Download the following patch and recompile the Zabbix server.

apt-get install patch
cd /usr/src/zabbix-1.8.5
wget http://lab4.org/files/zabbix-1.8.5-check-db-sql.diff
patch src/zabbix_server/poller/checks_db.c zabbix-1.8.5-check-db-sql.diff

The patch command should terminate without errors

patching file src / zabbix_server / poller / checks_db.c

If the patch has been applied successfully, you can compile the Zabbix server as usual with the command make. If you apply the patch to the directory where you have compiled Zabbix server before, then you do not need to run the configure command again. There should be a file config.status with all settings from the last compilation.

After the Zabbix-Server was recompiled with make, you should not call make install. In the subdirectory ./src/zabbix_server/zabbix_server you will find the new patched program file that you should exchange with the current version. Keep a backup of your old file.

make
/etc/init.d/zabbix-server stop
mv /opt/zabbix_server/sbin/zabbix_server /opt/zabbix_server/sbin/zabbix_server.bak
cp ./src/zabbix_server/zabbix_server /opt/zabbix_server/sbin/
/etc/init.d/zabbix-server start

Make sure that the Zabbix-server works as usual after patching. If this is the case, create a new item of type Database Monitor. Make sure that the SQL command is the last option in the "Additional parameters". Now you can use line breaks in your query.

Database monitor linebreak.png

Links

A Russian-language entry in the wiki helped me to successfully use ODBC in Zabbix. Tanks to Google's Translate (Russian -> English)

http://translate.google.de/translate?js=n&prev=_t&hl=de&ie=UTF-8&layout=2&eotf=1&sl=ru&tl=en&u=http%3A%2F%2Fwww.zabbix.com%2Fwiki%2Fnon-english%2Fru%2Fodbc_monitoring