9 Database engine

9.2.1 Oracle driver#

Connection string.

Passed as is to the Oracle client, so it accepts any form the client accepts:

ConnectionString = oraprod                          # tnsnames.ora alias
ConnectionString = dbhost.example.com:1521/ORAPROD  # Easy Connect
ConnectionString = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAPROD)))

To use aliases, the service’s TNS_ADMIN variable must point to the tnsnames.ora directory.

ConnectOptions.

SYSDBA connects with administrator privileges (required for ASM). Empty for a normal connection.

Contexts.

Container: the current container (PDB), changed with ALTER SESSION SET CONTAINER. It is only available on Oracle 12c or later, on a multitenant database and when connected to the root container (CDB$ROOT). In any other case the driver supports no contexts and ORACLE.CONTAINER is 0.

Bind variables.

Native Oracle (:name).

9.2.2 PGsql driver#

Connection string.

libpq key=value pairs separated by spaces (host, port, dbname, sslmode, connect_timeout…). The user and password are added from UserName and Password; do not put them in the string.

ConnectionString = host=pg01.example.com port=5432 dbname=postgres sslmode=require

ConnectOptions.

Not used.

Contexts.

Database: the current database. Since PostgreSQL cannot switch databases within a connection, the driver opens one connection per database used (replacing the string’s dbname) and keeps them open.

Bind variables.

Converted to $1, $2…

9.2.3 ODBC driver#

Connection string.

Passed to SQLDriverConnect, appending ;UID=…;PWD=… with the user and password. It can refer to a data source in odbc.ini or describe the full connection:

ConnectionString = DSN=MSSQL_PROD
ConnectionString = DRIVER={FreeTDS};SERVER=mssql.example.com;PORT=1433;DATABASE=master

To use DSN, the service’s ODBCINI variable must point to the odbc.ini file.

ConnectOptions.

Not used.

Contexts.

Database: the current database (catalogue), changed within the same connection with the ODBC attribute SQL_ATTR_CURRENT_CATALOG. It works with drivers that support it, such as the SQL Server ones.

Bind variables.

Converted to ?.

9.2.4 Runtime data#

Runtime data is read in <when> conditions with runtime(’NAME’) (section 9.6) and in queryexplorer with getRuntimeValue.

This way, the same XML file can choose the SQL by product (runtime(’DB.DATABASE’) = ’Microsoft SQL Server’), by version (major(runtime(’DB.VERSION’)) >= 12) or by whether the database is multitenant (runtime(’ORACLE.CONTAINER’) = ’1’).

9.4.1 Basic query#

The statement returns exactly one row. Each <item> takes the value of one column.

<query name="PGWriter" module="PG">
    <sql><![CDATA[
        select checkpoints_timed, checkpoints_req from pg_stat_bgwriter
        ]]>
    </sql>
    <item name="tez.pg.checkpointtimed" column="checkpoints_timed"/>
    <item name="tez.pg.checkpointreq"   column="checkpoints_req"/>
</query>

If the statement returns zero rows or more than one, all the query’s items become not supported.

9.4.2 Filter query (select="FILTER")#

The statement returns many rows. Each <parameter> with column links a key parameter with a column, and each item takes the row whose columns match its key parameters. The statement runs only once for all items, so this is the most efficient way to watch collections of objects (tablespaces, databases, jobs…).

<query name="PGSessions" module="PG" select="FILTER">
    <sql><![CDATA[
        select datname,
               count(st.pid) as n,
               sum(case st.state when 'active' then 1 else 0 end) as active
        from pg_database db
             left outer join pg_stat_activity st using (datname)
        where datname not like 'template%'
        group by datname
        ]]>
    </sql>
    <parameter column="datname" order="1" type="STRING"/>
    <item name="tez.pg.sessions"       column="n"/>
    <item name="tez.pg.sessionsactive" column="active" default="0"/>
</query>

The first four keys are resolved with a single execution of the statement. The comparison is exact: it is case-sensitive (Sales does not match sales) and only trims spaces at the ends of the parameter. If a column is space-padded (char type), trim it in the SQL with trim().

<query name="MSStateRepl" module="MSSQL" select="FILTER">
    <sql><![CDATA[ exec sp_replmonitorhelppublication ]]></sql>
    <default context="Database" value="distribution"/>
    <parameter column="publisher_db" order="1" type="STRING"/>
    <parameter column="publication"  order="2" type="STRING"/>
    <item name="tez.mssql.repl.status" column="status"/>
</query>

<parameter column="job_id" order="1" type="INT"/>
<item name="local.job.status" column="status"/>

9.4.3 Bind variable query (select="BIND")#

The key parameter is passed to the statement as a bind variable. The statement runs once for each distinct key and must return exactly one row.

<query name="PGParameter" module="PG" select="BIND">
    <sql><![CDATA[
        select setting from pg_settings where name = :name
        ]]>
    </sql>
    <parameter bind=":name" order="1" type="STRING"/>
    <item name="tez.pg.param" column="setting"/>
</query>

The variable is always written as :name, whatever the driver (section 9.2). With type="INT", the value is bound as a number.

9.4.4 Discovery query#

Each result row is a discovered entity, and each <field> takes the value of a column (column) or of a context (context).

<query name="TSDiscover" module="DB" discovery="yes">
    <sql><![CDATA[
        select tablespace_name, contents
        from dba_tablespaces where status = 'ONLINE'
        ]]>
    </sql>
    <item name="tez.ora.tspace.discovery"/>
    <field name="TSNAME"   column="tablespace_name"/>
    <field name="CONTENTS" column="contents"/>
</query>

Value of the item tez.ora.tspace.discovery:

{"data":[
  {"{#TSNAME}":"SYSTEM", "{#CONTENTS}":"PERMANENT"},
  {"{#TSNAME}":"USERS",  "{#CONTENTS}":"PERMANENT"},
  {"{#TSNAME}":"TEMP",   "{#CONTENTS}":"TEMPORARY"}
]}

In Zabbix, the prototype tez.ora.tspace.pctfree[{#TSNAME}] creates the items tez.ora.tspace.pctfree[SYSTEM], [USERS] and [TEMP], resolved by a FILTER query with parameter column="tablespace_name".

Discoveries only accept context parameters.

<query name="MSDiscoverFG" module="MSSQL" discovery="yes">
    <sql context="Database"><![CDATA[
        select name from sys.databases where name not in ('model')
        ]]>
    </sql>
    <sql><![CDATA[
        select name from sys.filegroups
        ]]>
    </sql>
    <item name="tez.mssql.fg.discovery"/>
    <field name="DBNAME" context="Database"/>
    <field name="FGNAME" column="name"/>
</query>

{"data":[
  {"{#DBNAME}":"master", "{#FGNAME}":"PRIMARY"},
  {"{#DBNAME}":"sales",  "{#FGNAME}":"PRIMARY"},
  {"{#DBNAME}":"sales",  "{#FGNAME}":"HISTORY"}
]}

9.5.1 Declaration#

Contexts are declared at <Queries> level:

<environment name="PGAdminDB" default="postgres"/>
<context name="Database" module="PG" default="${PGAdminDB}"/>

9.5.2 Use in queries#

There are three ways to choose the context of a query:

1. Default. If the query says nothing, it runs with the context’s default value.

2. Fixed for the query, with <default>:

   <default context="Database" value="msdb"/>

   If value is __IGNORE__ (for example, through an unset environment variable), the element is ignored and the default value is kept.

3. Taken from the item key, with a context parameter:

   <parameter context="Database" order="1"/>

9.5.3 Example: a filter inside each database#

With a context parameter, the first key parameter selects the database and the following ones select the row:

<context name="Database" module="MSSQL" default="master"/>

<query name="MSFGSpace" module="MSSQL" select="FILTER">
    <sql><![CDATA[
        select g.name as filegroup_name,
               sum(cast(fileproperty(f.name, 'SPACEUSED') as bigint) * 8192) as used
        from sys.database_files f
             inner join sys.filegroups g on g.data_space_id = f.data_space_id
        group by g.name
        ]]>
    </sql>
    <parameter context="Database" order="1"/>
    <parameter column="filegroup_name" order="2" type="STRING"/>
    <item name="tez.mssql.fgspace.used" column="used"/>
</query>

Databbix groups executions by context: it enters sales only once, runs the statement and resolves the first two keys with the same result. If it cannot enter a context, it marks the items of that context as not supported. If the failing context is the default one and ConnLostOnDefaultCtxError = yes, the connection is considered lost and re-established.

9.7.1 Example: Oracle#

[General]
Name = ORAPROD

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

[Data]
Module = DB
Module = Instance

[Database]
DBDriver = Oracle
ConnectionString = oraprod          # tnsnames.ora alias
UserName = zabbix
Password = secret

9.7.2 Example: Oracle multitenant#

[General]
Name = ORACDB
QueryFile = /etc/databbix/query-mt.xml

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

[Data]
Module = MT

[Database]
DBDriver = Oracle
ConnectionString = oracdb
UserName = c\043\043zabbix          # c##zabbix
Password = secret

9.7.3 Example: ASM#

[General]
Name = asm

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

[Data]
Module = ASM

[Database]
DBDriver = Oracle
ConnectionString = asm
ConnectOptions = SYSDBA
UserName = sys
Password = secret

9.7.4 Example: PostgreSQL#

[General]
Name = PGPROD
QueryFile = /etc/databbix/query-pg.xml
QueryFile = /etc/databbix/query-pg-10.xml
Environment = PGAdminDB=postgres

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

[Data]
Module = PG

[Database]
DBDriver = PGsql
ConnectionString = host=pg01.example.com port=5432 dbname=postgres
UserName = zabbix
Password = secret

9.7.5 Example: SQL Server through ODBC#

First, define the data source in /etc/databbix/odbc.ini:

[MSSQL_PROD]
Driver=FreeTDS
Description=Production SQL Server
Server=mssql.example.com
Port=1433
Database=master

Then the monitor refers to it by name:

[General]
Name = MSSQLPROD
QueryFile = /etc/databbix/query-mssql.xml

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

[Data]
Module = MSSQL

[Database]
DBDriver = ODBC
ConnectionString = DSN=MSSQL_PROD
UserName = zabbix
Password = secret