JDBCStoredProcedureMonitor

The JDBCStoredProcedureMonitor checks the result of a stored procedure in a remote database. The result of the stored procedure has to be a boolean value (representing true or false). The service associated with this monitor is marked as up if the stored procedure returns true and it is marked as down in all other cases. It is based on the JDBC technology to connect and communicate with the database.

Monitor facts

Class Name

org.opennms.netmgt.poller.monitors.JDBCStoredProcedureMonitor

Remote Enabled

false

Configuration and Usage
Table 1. Monitor specific parameters for the JDBCStoredProcedureMonitor
Parameter Description Required Default value

driver

JDBC driver class to use

required

com.sybase.jdbc2.jdbc.SybDriver

url

JDBC Url to connect to.

required

jdbc:sybase:Tds:OPENNMS_JDBC_HOSTNAME/tempdb

user

Database user

required

sa

password

Database password

required

empty string

timeout

Timeout in ms for the database connection

optional

3000

retries

How many retries should be performed before failing the test

optional

0

stored-procedure

Name of the database stored procedure to call

required

-

schema

Name of the database schema in which the stored procedure is

optional

test

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.
Provide the database driver

The JDBCStoredProcedureMonitor is based on JDBC and requires a JDBC driver to communicate with any database. Due to the fact that OpenNMS HORIZON itself uses a PostgreSQL database, the PostgreSQL JDBC driver is available out of the box. For all other database systems a compatible JDBC driver has to be provided to OpenNMS HORIZON as a jar-file. To provide a JDBC driver place the driver-jar in the opennms/lib folder of your OpenNMS HORIZON. To use the JDBCStoredProcedureMonitor from a remote poller, the driver-jar has to be provided to the Remote Poller too. This may be tricky or impossible when using the Java Webstart Remote Poller, because of code signing requirements.

Examples

The following example checks a stored procedure added to the PostgreSQL database used by OpenNMS HORIZON. The stored procedure returns true as long as less than 250000 events are in the events table of OpenNMS HORIZON.

Stored procedure which is used in the monitor
CREATE OR REPLACE FUNCTION eventlimit_sp() RETURNS boolean AS
$BODY$DECLARE
num_events integer;
BEGIN
        SELECT COUNT(*) into num_events from events;
        RETURN num_events > 250000;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;
<service name="OpenNMS-DB-SP-Event-Limit" interval="300000" user-defined="true" status="on">
  <parameter key="driver" value="org.postgresql.Driver"/>
  <parameter key="url" value="jdbc:postgresql://OPENNMS_JDBC_HOSTNAME:5432/opennms"/>
  <parameter key="user" value="opennms"/>
  <parameter key="password" value="opennms"/>
  <parameter key="stored-procedure" value="eventlimit_sp"/>
  <parameter key="schema" value="public"/>
</service>

<monitor service="OpenNMS-DB-SP-Event-Limit" class-name="org.opennms.netmgt.poller.monitors.JDBCStoredProcedureMonitor"/>