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 |
|
Remote Enabled |
false |
Configuration and Usage
Parameter | Description | Required | Default value |
---|---|---|---|
|
JDBC driver class to use |
required |
|
|
JDBC Url to connect to. |
required |
|
|
Database user |
required |
|
|
Database password |
required |
|
|
Timeout in ms for the database connection |
optional |
|
|
How many retries should be performed before failing the test |
optional |
|
|
Name of the database stored procedure to call |
required |
|
|
Name of the database schema in which the stored procedure is |
optional |
|
Note
|
The OPENNMS_JDBC_HOSTNAME is replaced in the url parameter 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 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 as a jar-file.
To provide a JDBC driver place the driver-jar in the opennms/lib
folder of your OpenNMS.
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. The stored procedure returns true as long as less than 250000 events are in the events table of OpenNMS.
Stored procedure
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"/>