← Databbix Example

Metrics from any database table

If a number can be calculated with a select, it can be a Zabbix item. This example takes a table of orders and turns it into items, a graph and an alert, per sales channel, writing only SQL and a few lines of XML.

1: the orders table with a channel column. 2: the XML query that counts orders per channel in the last hour. 3: the Zabbix item shop.orders.count[web], last value 312. 4: the Zabbix graph of the item over 24 hours, with a drop to zero that raised a problem.

The data

An online shop stores every order in a table called orders. Each row has the time it was created, the sales channel (web, physical store, mobile app) and the amount.

We want to know, for each channel, how many orders came in during the last hour, see it on a graph and get an alert if a channel stops selling.

The database user that Databbix uses only needs read permission on this table.

id	created	channel	amount
10421	2026-09-30 12:58:02	web	49.90
10422	2026-09-30 12:58:40	store	120.00
10423	2026-09-30 12:59:11	web	15.50
10424	2026-09-30 12:59:37	app	32.00
10425	2026-09-30 13:00:05	web	78.25

The query

Queries live in XML files. Create your own, for example /etc/databbix/query-shop.xml, so that package updates never overwrite it.

select="FILTER": the statement returns one row per channel, and each Zabbix item picks its row.
<parameter column="channel">: the parameter of the item key, [web], is compared with the channel column.
<item>: each item takes the value of one column. One query can feed as many items as columns you return: here, number of orders and amount.
default="0": if a channel has no orders in the last hour, it has no row, and the item is 0 instead of “not supported”. That is exactly what makes the alert possible.
maxCache="60": the result is reused for 60 seconds.

The first query is a discovery: it lists the channels, so Zabbix creates the items for each one automatically, including new channels in the future.

/etc/databbix/query-shop.xml

<?xml version="1.0" encoding="UTF-8"?>
<Queries engine="Database" query="2.5.0">

<!-- One item per sales channel, created by Zabbix -->
<query name="ShopChannels" module="SHOP" discovery="yes">
    <sql><![CDATA[
        select code as channel
        from sales_channels
        where active
    ]]></sql>
    <item name="shop.channel.discovery"/>
    <field name="CHANNEL" column="channel"/>
</query>

<!-- Orders and amount per channel, last hour -->
<query name="ShopOrders" module="SHOP"
       select="FILTER" maxCache="60">
    <sql><![CDATA[
        select channel,
               count(*)    as orders,
               sum(amount) as amount
        from orders
        where created > now() - interval '1 hour'
        group by channel
    ]]></sql>
    <parameter column="channel" order="1" type="STRING"/>
    <item name="shop.orders.count"  column="orders" default="0"/>
    <item name="shop.orders.amount" column="amount" default="0"/>
</query>

</Queries>

The monitor

A monitor file connects to the database and says which Zabbix host receives the values. Name must match the Host name of the host in Zabbix.

Save it in /etc/databbix/dbs/ and restart the service: Databbix starts one monitor per file.

/etc/databbix/dbs/shopdb.conf

[General]
Name             = SHOPDB
Engine           = Database
QueryFile        = /etc/databbix/query-shop.xml

[Zabbix]
Server           = zabbix.example.com
Port             = 10051

[Data]
Module           = SHOP

[Database]
DBDriver         = PGsql
ConnectionString = host=shopdb dbname=shop
UserName         = zabbix
Password         = ********

Test it

Before touching Zabbix, queryexplorer loads the same configuration and computes any item key on demand, exactly as the monitor would. If the XML or the connection has a problem, it tells you straight away.

Note the last line: the kiosk channel has no orders, so the item gets its default value.

queryexplorer

$ sudo -u databbix /opt/trustserver/bin/queryexplorer -l -f shopdb.conf

QueryExplorer> get shop.orders.count[web]
Item: shop.orders.count[web], value: 312
QueryExplorer> get shop.orders.amount[web]
Item: shop.orders.amount[web], value: 15840.35
QueryExplorer> get shop.orders.count[kiosk]
Item: shop.orders.count[kiosk], value: 0

In Zabbix

On the host SHOPDB (or better, in a template), create:

A discovery rule of type Zabbix agent (active) with key shop.channel.discovery.
Two item prototypes, also Zabbix agent (active): shop.orders.count[{#CHANNEL}] and shop.orders.amount[{#CHANNEL}].
A trigger prototype that fires when a channel stops selling.

From then on you have the history of every channel, graphs, dashboards and alerts, with the same tools you use for your servers.

A drop to zero in a business metric is often the first sign of a technical failure: a payment gateway down, a broken deployment, an expired certificate. In the graph above, the alert fired while every server was still green.

Zabbix · trigger prototype

# Name
No {#CHANNEL} orders in the last hour

# Expression
last(/SHOPDB/shop.orders.count[{#CHANNEL}])=0

# Resulting items
shop.orders.count[web]      shop.orders.amount[web]
shop.orders.count[store]    shop.orders.amount[store]
shop.orders.count[app]      shop.orders.amount[app]

Same recipe, any metric

Change the SQL and you have a different indicator. A few ideas that work the same way:

Pending invoices

Invoices not yet sent to the tax agency or the customer, per company, with an alert when the backlog grows.

Stock below minimum

Number of products under their reorder point, per warehouse.

Failed payments

Rejected transactions in the last few minutes, per payment method.

Stuck jobs and queues

Batch jobs in error, messages waiting in an integration table, age of the oldest pending record.

Service levels

Average time between a ticket or order being created and being handled, per team.

Data freshness

Minutes since the last row was loaded by an ETL or a replication process.

Database engine in the manual Practical guide: creating a new query