14 Practical guide: creating a new query

14.1 Step 1: write and test the SQL#

First, write the query and test it with the same user Databbix uses, for example with SQL*Plus or psql. Check that it returns one row per monitored object and that the columns have clear aliases:

select owner as schema_name, count(*) as invalid_count
from dba_objects
where status = 'INVALID'
group by owner;

14.2 Step 2: choose the query type#

• Does it always return one row? → basic query.

• Does it return one row per object, and you want items per object? → FILTER.

• Is the set of possible values huge and only a few are of interest? → BIND.

• Do you want Zabbix to create the items automatically? → a discovery query plus a FILTER one.

In the example, a schema with no invalid objects does not appear in the result, so FILTER is used with default="0", plus a discovery for the schemas.

14.3 Step 3: write the XML#

It is best to create your own file instead of modifying the distribution’s files, which are overwritten on upgrade. Save it as /etc/databbix/query-local.xml:

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

<query name="LocalSchemaDiscovery" module="DB" discovery="yes">
    <sql><![CDATA[
        select username as schema_name
        from dba_users
        where oracle_maintained = 'N'
        ]]>
    </sql>
    <item name="local.ora.schema.discovery"/>
    <field name="SCHEMA" column="schema_name"/>
</query>

<query name="LocalInvalidObjects" module="DB" select="FILTER" maxCache="60">
    <sql><![CDATA[
        select owner as schema_name, count(*) as invalid_count
        from dba_objects
        where status = 'INVALID'
        group by owner
        ]]>
    </sql>
    <parameter column="schema_name" order="1" type="STRING"/>
    <item name="local.ora.invalid" column="invalid_count" default="0"/>
    <item name="local.ora.invalid.exectime" built_in="exec_time"/>
</query>

</Queries>

14.4 Step 4: add the file to the monitor#

QueryFile in the monitor replaces the global list, so the main file must be included too:

[General]
Name = ORAPROD
QueryFile = /etc/databbix/query.xml
QueryFile = /etc/databbix/query-local.xml

14.5 Step 5: test with queryexplorer#

Before creating anything in Zabbix, test the query with queryexplorer (chapter 13), without touching the service:

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

QueryExplorer> get local.ora.schema.discovery
QueryExplorer> get local.ora.invalid[HR]
QueryExplorer> get local.ora.invalid.exectime[HR]

If the XML file has an error, queryexplorer never shows the prompt and the message explains which query fails. If a value is not as expected, repeat with -v 9 to see the SQL and the rows obtained. When everything is correct, restart the service so that the monitor loads the new file:

systemctl restart databbix

14.6 Step 6: create the items in Zabbix#

On the ORAPROD host (or in a template):

1. A discovery rule of type Zabbix agent (active) with key:
   local.ora.schema.discovery

2. A numeric item prototype of type Zabbix agent (active) with key:
   local.ora.invalid[{#SCHEMA}]

3. Optionally, a trigger prototype, for example:
   last(/ORAPROD/local.ora.invalid[{#SCHEMA}])>0

Data arrives when Databbix next requests the active item list, that is, within Zabbix.Refresh seconds at most (300 by default), or immediately if the service is restarted.