Sometimes it is required to monitor not only a performance of database, but also the data inside it. Zabbix has native functionality to query the database, visualize and process the data.
Watch the video now.
Today we will talk about database engine monitoring. This tutorial, however, is not about monitoring the performance of a database (memory usage, CPU usage, etc.), but about monitoring the data inside it. This is an absolutely native, out-of-the-box functionality in Zabbix, so there is no need for additional scripting or custom items. It works over ODBC.
What we will do is configure ODBC monitoring and create basic, default items in the front end with SQL queries to retrieve data. In this tutorial, I will be using MySQL. I have Zabbix 4.0 pre‐installed, CentOS 7.5 virtual machine, and MariaDB server. This time I don’t have anything pre-configured or pre‐installed in terms of ODBC.
ODBC connection configuration
You will need to use the ODBC monitoring page in Zabbix Documentation because we are going to copy‐paste a couple of things.
First of all, we will install unixODBC and unixODBC-devel. Just copy-paste the line to your CLI and don’t forget to add ‘–y‘ to confirm.
yum −y install unixODBC unixODBC−devel –y
Each database engine needs its own ODBC connector driver:
yum install mysql−connector−odbc
Note. This line works for MySQL or MariaDB. If you have Microsoft SQL, search for freetds (it is not available in the CentOS default repository). If you have another database, search the web for the appropriate driver and connector.
Then I need to configure ODBC, which can be done with this line:
It comes from unixODBC−devel. It will, first of all, show us the unixODBC version and the location of the drivers:
As you can see below, inside odbcinst.ini I have two drivers, one for PostgreSQL and one for MySQL. I will be using the latter.
You can also see that system data sources are defined inside odbc.ini:
However, we need to create this file first:
Inside this file, in brackets we need to specify the DSN name that we will later use in the front end. I will call it ‘testing‘.
Then we need to add a couple of parameters copy-pasted from the Zabbix Documentation:
Driver = mysql Server = 127.0.0.1 User = root Password = Port = 3306 Database = zabbix
I need to specify the user, which is ‘zabbix‘, and the password, which is also ‘zabbix‘. The rest is unchanged:
Driver = mysql Server = 127.0.0.1 User = zabbix Password = zabbix Port = 3306 Database = zabbix
Finally, save this file with :wq:.
To verify if ODBC connection is working successfully, type:
The DSN name is the name you specified in odbc.ini. In my case, it is ‘testing‘.
As you can see from the output, I am right now connected to our Zabbix database through ODBC, not through the default MySQL connector. I can access the database and run any query.
Let’s go to the front end and create a new host (Configuration > Hosts > Create Hosts).
We don’t have to specify any interfaces because we are using only database monitoring items and none of those items utilize agent, SNMP, JMX or IPMI interfaces.
Now let’s create an item. Go to Configuration > Hosts > Your Host Name > Items > Create Item.
Name and Type both will be ‘Database monitor‘.
We also need to specify the Key following this syntax:
db.odbc.select[<unique short description>,<dsn>]
We can delete ‘<unique short description>‘ for now because it is optional, but we are going to keep the second parameter ‘<dsn>‘, which is ‘testing‘ in my case.
There is no need to specify the User name or Password because we have them in our odbc.ini file.
Note. In some cases, it might be reasonable to specify this in the item and not in the CLI because odbc.ini is a plain text file that stores database credentials and is accessible by the Zabbix user at least.
Enter the SQL query that will be executed inside the database, and the output of that query will be taken as a value inside this item, for example:
Partly, it will work. However, there is a limitation on db.odbc.select items which can return just one single value. So, if you run this query, it technically returns just the first row of the first column.
Let’s run this query, but do not forget to choose the right type of information first.
Click Add and go to Monitoring > Latest data > Your Host Name > History. You can see the history and, as I said, it is just the first value of the first column.
So, you are not getting the rest of the information. That is why your query in Zabbix versions up to 4.4 should always be formed, for example, like this:
You need to create more complicated queries to receive just one single value you are looking for.
But there is another option. We have two users — Admin Zabbix and guest. So our query might look like this:
We get two columns — the name and the surname. But there is also an option where both values are displayed, we simply need to change our query a bit.
I will return to the Database monitor item. This is the case when you need to use the unique short description parameter.
Remember that the Key must be unique for an item, so you cannot have two items in one host or one template with absolutely identical keys. To make keys unique, we need to use a description, for example:
If I run the query above, then the received value would be ‘Zabbix‘. However, I want to receive ‘Zabbix Administrator‘, so I can use a query like this which concatenates two columns:
And, what if I want to add a space between the name and the surname? No problem, just add a space in quotes:
This query technically returns two columns but still, I will be receiving both lines of that column. You can see the result in History:
You can also use low‐level discovery, which was discussed in our previous post. What you can do is run a query and, based on the result, you will receive LLD macros so you can create an item manually to automate all of these steps and make it a lot easier.
Anyway, it is still possible to create just single regular items to extract some kind of data from your database. The type of data depends on the database you are running, e.g. for a website, an application, for banking, etc.
That’s it for today. Thank you and good luck.