JDBCQueryMonitor

The JDBCQueryMonitor runs an SQL query against a database and is able to verify the result of the query. A read-only connection is made, preventing the monitor from being able to alter data. It is based on the JDBC technology to connect and communicate with the database.

Monitor facts

Class Name

org.opennms.netmgt.poller.monitors.JDBCQueryMonitor

Configuration and use

Table 1. Monitor-specific parameters for the JDBCQueryMonitor
Parameter	Description	Default
Required
url `{}`	JDBC URL to connect to.	jdbc:postgresql://OPENNMS_JDBC_HOSTNAME/opennms
user `{}`	Database user	postgres
password `{}`	Database password	empty string
query	The SQL query to run.	n/a
action	The evaluation action to perform.	row_count
column	The result column to evaluate against when using compare_string method.	n/a
operator	Operator to use for the evaluation.	>=
operand	The operand to compare against the SQL query result.	depends on the action
Optional
message	The message to use if the service is down. Both operands and the operator are added to the message.	generic message, depending on the action
retries	How many retries to perform before failing the test.	0
include-query	Whether or not to include the query executed in the message used if the service is unavailable.	false
include-first-result	Whether or not to include the first row of results from the query executed in the message used when the service is unavailable.	false
include-all-results	Whether or not to include all rows of results from the query executed in the message used when the service is unavailable.	false
line-separator	The string to use to separate lines in the message used when the service is unavailable.	"\n"
column-separator	The string to use to separate columns in query results of the message used when the service is unavailable.	"; "
column-key-separator	The string to use to separate the column key from the column value in query results of the message used when the service is unavailable.	": "

{} indicates the parameter supports placeholder substitution.

The OPENNMS_JDBC_HOSTNAME is replaced in the url parameter with the IP or resolved hostname of the interface the monitored service is assigned to.

This monitor implements the Common Configuration Parameters.

Table 2. Available action parameters and their default operand
Parameter	Description	Default operand
row_count	Compares the number of returned rows, not a value of the resulting rows.	1
compare_string	Always checks strings for equality with the operand.	n/a
compare_int	Compares an integer from a column of the first result row.	1
compare_bool	Compares an integer from a column of the first result row. The operator is ignored for this action with equality assumed.	true

Table 3. Available operator parameter values
Parameter	XML entity to use in XML configs
=	=
<	<
>	>
!=	!=
⇐	<=
>=	>=

Evaluate the action - operator - operand

Evaluates only the first result row the SQL query returns. The evaluation can be against the value of one column or the number of rows the SQL query returns.

Provide the database driver

The JDBCQueryMonitor is based on JDBC and requires a JDBC driver to communicate with any database. Since Horizon itself uses a PostgreSQL database, the PostgreSQL JDBC driver is available out of the box. For all other database systems, you must provide a compatible JDBC driver to Horizon as a JAR file. To provide a JDBC driver, place the driver-jar in your $OPENNMS_HOME/lib folder. For instructions on how to provide JDBC drivers on Minion, see Install JDBC driver.

Examples

Row count

The following example checks if the number of events in the Horizon database is fewer than 250,000.

Note that you must include the monitor section in your definition.

<service name="OpenNMS-DB-Event-Limit" interval="30000" user-defined="true" status="on">
  <parameter key="url" value="jdbc:postgresql://OPENNMS_JDBC_HOSTNAME:5432/opennms"/> (2)
  <parameter key="user" value="opennms"/> (3)
  <parameter key="password" value="opennms"/> (4)
  <parameter key="query" value="select eventid from events" /> (5)
  <parameter key="action" value="row_count" /> (6)
  <parameter key="operand" value="250000" /> (7)
  <parameter key="operator" value="&lt;" /> (8)
  <parameter key="message" value="too many events in OpenNMS database" /> (9)
</service>

<monitor service="OpenNMS-DB-Event-Limit" class-name="org.opennms.netmgt.poller.monitors.JDBCQueryMonitor" /> (10)

1	JDBC URL to connect to.
2	Database user.
3	Database password.
4	The SQL query to run.
5	The evaluation action to perform.
6	The operand to compare against the SQL query result.
7	Operator to use for the evaluation.
8	The message to use if the service is down.
9	Required monitor section.

String comparison

The following example checks if the queried string matches against a defined operand.

Note that you must include the monitor section in your definition.

<service name="MariaDB-Galera" interval="300000" user-defined="false" status="on">
  <parameter key="user" value="opennms"/> (2)
  <parameter key="password" value="********"/> (3)
  <parameter key="url" value="jdbc:mysql://OPENNMS_JDBC_HOSTNAME"/> (4)
  <parameter key="query" value="SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'"/> (5)
  <parameter key="column" value="VARIABLE_VALUE"/> (6)
  <parameter key="action" value="compare_string"/> (7)
  <parameter key="operator" value="="/> (8)
  <parameter key="operand" value="Primary"/> (9)
  <parameter key="message" value="Galera Node is not in primary component"/> (10)
</service>

<monitor service="MariaDB-Galera" class-name="org.opennms.netmgt.poller.monitors.JDBCQueryMonitor" /> (11)

1	Database user.
2	Database password.
3	JDBC URL to connect to.
4	The SQL query to run.
5	The result column to evaluate against when using compare_string method.
6	The evaluation action to perform.
7	Operator to use for the evaluation.
8	The operand to compare against the SQL query result.
9	The message to use if the service is down.
10	Required monitor section.