From OpenNMS

This poller monitor calls a stored procedure in a remote database and marks a service Up if the return value is true or Down if it is false. The wonderful thing it is that it enables the OpenNMS administrator to be lazy while the DBAs do all the heavy lifting.

Contents 1 JDBC Stored Procedure Monitor Parameters 1.1 Parameters specific to the JDBCStoredProcedureMonitor 1.2 Parameters common to all poller monitors 2 Example configs 2.1 PostgreSQL 2.2 MySQL 2.3 Microsoft SQL Server 3 Writing the Stored Procedure 4 Service Provisioning 4.1 Capsd 4.2 Provisioning Groups

JDBC Stored Procedure Monitor Parameters

Parameters specific to the JDBCStoredProcedureMonitor

port 

The port to use for this service. Note that the port used for the database connection is normally part of the JDBC URL defined in the url parameter.

driver 

The fully-qualified name of the JDBC driver class to use for this service, e.g. org.postgresql.Driver or com.microsoft.jdbc.sqlserver.SQLServerDriver.

url 

The JDBC URL for connections to the database used for this service. The format of these URLs is vendor-specific; here are links to documentation for PostgreSQL, MySQL, and MS SQL Server. If present in the value of this parameter, the token OPENNMS_JDBC_HOSTNAME will be replaced at runtime with the IP address of the interface currently being polled.

user 

The database username to use (along with the password parameter) when authenticating to the database used for this service.

password 

The database password to use (along with the user parameter) when authenticating to the database used for this service.

stored-procedure 

The name of the database stored procedure to call for this service.

schema 

The name of the database schema in which the stored procedure identified by the stored-procedure parameter exists. Optional; default value is test.

Parameters common to all poller monitors

retry

timeout

rrd-repository

rrd-base-name

ds-name

Example configs

PostgreSQL

        <service name="JDBCSPTestPostgreSQL" interval="300000" user-defined="false" status="on">
            <parameter key="retry" value="1"/>
            <parameter key="timeout" value="5000"/>
            <parameter key="port" value="5432"/>
            <parameter key="driver" value="org.postgresql.Driver"/>
            <parameter key="url" value="jdbc:postgresql://OPENNMS_JDBC_HOSTNAME:5432/dbname"/>
            <parameter key="user" value="dbuser"/>
            <parameter key="password" value="dbpass"/>
            <parameter key="rrd-repository" value="/var/lib/opennms/rrd/response"/>
            <parameter key="ds-name" value="pgsqltransaction"/>
            <parameter key="stored-procedure" value="test_sp"/>
             <parameter key="schema" value="public"/>
        </service>

    <monitor service="JDBCSPTestPostgreSQL" class-name="org.opennms.netmgt.poller.monitors.JDBCStoredProcedureMonitor"/>

MySQL

        <service name="JDBCSPTestMySQL" interval="300000" user-defined="false" status="on">
            <parameter key="retry" value="1"/>
            <parameter key="timeout" value="5000"/>
            <parameter key="port" value="5432"/>
            <parameter key="driver" value="com.mysql.jdbc.Driver"/>
            <parameter key="url" value="jdbc:mysql://OPENNMS_JDBC_HOSTNAME:3306/dbname"/>
            <parameter key="user" value="dbuser"/>
            <parameter key="password" value="dbpass"/>
            <parameter key="rrd-repository" value="/var/lib/opennms/rrd/response"/>
            <parameter key="ds-name" value="mysqltransaction"/>
            <parameter key="stored-procedure" value="test_sp"/>
             <parameter key="schema" value="public"/>
        </service>

    <monitor service="JDBCSPTestMySQL" class-name="org.opennms.netmgt.poller.monitors.JDBCStoredProcedureMonitor"/>

Microsoft SQL Server

        <service name="JDBCSPTestMSSQL" interval="300000" user-defined="false" status="on">
            <parameter key="retry" value="1"/>
            <parameter key="timeout" value="5000"/>
            <parameter key="port" value="5432"/>
            <parameter key="driver" value="com.microsoft.jdbc.sqlserver.SQLServerDriver"/>

<!-- v3 of the sqljdbc.jar specifies the class as 'com.microsoft.sqlserver.jdbc.SQLServerDriver' check the class file for the correct name if you get an error -->

            <parameter key="url" value="jdbc:sqlserver://OPENNMS_JDBC_HOSTNAME:1433;databaseName=AdventureWorks"/>
            <parameter key="user" value="dbuser"/>
            <parameter key="password" value="dbpass"/>
            <parameter key="rrd-repository" value="/var/lib/opennms/rrd/response"/>
            <parameter key="ds-name" value="mssqltransaction"/>
            <parameter key="stored-procedure" value="test_sp"/>
             <parameter key="schema" value="dbo"/>
        </service>

    <monitor service="JDBCSPTestMSSQL" class-name="org.opennms.netmgt.poller.monitors.JDBCStoredProcedureMonitor"/>

Writing the Stored Procedure

The stored procedure is really just an interface to code that is maintained by the DBAs. The internals of the stored procedure can be arbitrarily complex, as long as the return value can be boiled down to what amounts to a boolean. Some RDBMSes lack a BOOLEAN type; in these cases, BIT is the correct type to use, where a value of 1 represents true and 0 represents false.

The specifics of creating a stored procedure differ substantially among RDBMSes, so refer to your system's manual or offer doughnuts to your DBAs if you need help.

A simple SQL Server stored procedure that returns true as long as the fundamentals of mathematics are working properly would look like this; note the quotes around the return value, something apparently unique to MSSQL:

CREATE PROCEDURE dbo.test_sp AS
BEGIN
  declare @result bigint
  set @result = 0
  SELECT @result = 2 + 2
    IF @result = 4
      RETURN '1'
    ELSE 
      RETURN '0'
END
GO

Service Provisioning

Capsd

Discovering services monitored by this poller monitor is difficult. In the Capsd model, the easiest way to provision such services is to use the scan="enable" trick in capsd-configuration.xml:

    <protocol-plugin protocol="JdbcDbTest" class-name="org.opennms.netmgt.capsd.plugins.LoopPlugin" scan="off" user-defined="false">
        <protocol-configuration scan="enable" user-defined="false">
            <specific>10.11.12.13</specific>
            <specific>172.31.0.42</specific>
            <specific>192.168.42.42</specific>
        </protocol-configuration>
    </protocol-plugin>

Provisioning Groups

Adding these services via the Provisioning Groups feature of OpenNMS 1.3.2 and later is as simple as adding creating the service by adding an empty protocol-plugin to capsd-configuration.xml and then adding the service in a provisioning group on each interface where it should appear.

    <protocol-plugin protocol="JdbcDbTest" class-name="org.opennms.netmgt.capsd.LoopPlugin" scan="off" user-defined="false" />