I feel terrible. I thought I had sent out a notice about this, but I can’t find it anywhere, so here it is, better late than never.

José Vicente Nunez Zuleta has written a true JDBC poller for OpenNMS. Currently, OpenNMS just does a TCP port check on well known database ports. This is still the default, since true database polling requires a username and password, and since the chance of this being available out of the box is slim, we will continue to check ports.

However, once possible databases are discovered, they can be configured to allow OpenNMS to poll them using a true synthetic transaction. The poller will connect and attempt to get the database catalog. If this is successful, the poll is sucessful – otherwise it fails.

In order to automatically detect and monitor databases, a few changes need to be made to both the network and the OpenNMS configuration. First, be sure that the username and password you plan to use actually works from the OpenNMS server. This will involve changes to pg_hba.conf for PostgreSQL, and I am not sure about others.

Second, you will need to insure that you have a jar file with the JDBC driver for your particular database. Copy it to $OPENNMS_HOME/lib (the one for PostgreSQL is already included).

Okay, now you need to modify the capsd configuration to discover the service and modify the poller configuration to poll the service.

Capsd: Here’s an example for Sybase:

and one for MySQL:

<protocol-plugin protocol="MySQL-JDBC" class-name="org.opennms.netmgt.capsd.plugins.JDBCPlugin" scan="on">
        <property key="user" value="root"/>
        <property key="password" value="XXXX"/>
        <property key="retry" value="3"/>
        <property key="timeout" value="5000"/>
        <property key="driver" value="org.gjt.mm.mysql.Driver"/>
        <!-- jdbc:mysql://[]/ -->
        <property key="url" value="jdbc:mysql://OPENNMS_JDBC_HOSTNAME:3306/mysql"/>
  </protocol-plugin>

And one for PostgreSQL:

  <protocol-plugin protocol="PostgreSQL-JDBC" class-name="org.opennms.netmgt.capsd.plugins.JDBCPlugin" scan="on">
        <property key="user" value="opennms"/>
        <property key="password" value="opennms"/>
        <property key="retry" value="3"/>
        <property key="timeout" value="5000"/>
        <property key="driver" value="org.postgresql.Driver"/>
        <!-- jdbc:postgresql:[[:/]] -->
        <property key="url" value="jdbc:postgresql://OPENNMS_JDBC_HOSTNAME:5432/opennms"/>
  </protocol-plugin>

Note that the service names for all three of these examples have “-JDBC” added to the end of their names. This means you can run them separately from the standard database protocols, or if you like, you can completely replace the standard protocols. In fact, if you wish, you can use the standard port check in capsd, and then use the JDBC poller configuration to do the actual polling.

Here are the poller configuration examples:

    <service name="Sybase-JDBC" user-defined="false" interval="6000" status="on">
        <parameter key="user" value="sa"/>
        <parameter key="password" value="XXXX"/>
        <parameter key="timeout" value="3000"/>
        <parameter key="driver" value="com.sybase.jdbc2.jdbc.SybDriver"/>
        <!-- jdbc:sybase:Tds::/ -->
        <parameter key="url" value="jdbc:sybase:Tds:OPENNMS_JDBC_HOSTNAME:4100/tempdb"/>
    </service>

    <service name="MySQL-JDBC" user-defined="false" interval="6000" status="on">
        <parameter key="user" value="root"/>
        <parameter key="password" value="XXXX"/>
        <parameter key="timeout" value="3000"/>
        <parameter key="driver" value="org.gjt.mm.mysql.Driver"/>
        <!-- jdbc:mysql://[]/ -->
        <parameter key="url" value="jdbc:mysql:// OPENNMS_JDBC_HOSTNAME:3306/mysql"/>
    </service>

    <service name="PostgreSQL-JDBC" user-defined="false" interval="9000" status="on">
        <parameter key="user" value="opennms"/>
        <parameter key="password" value="opennms"/>
        <parameter key="timeout" value="9000"/>
        <parameter key="driver" value="org.postgresql.Driver"/>
        <!-- jdbc:postgresql:[[:/]] -->
        <parameter key="url" value="jdbc:postgresql://OPENNMS_JDBC_HOSTNAME:5432/opennms"/>
    </service>

One more thing in the poller-configuration file, you’ll need to add tags at the bottom:

  <monitor service="Sybase-JDBC" class-name="org.opennms.netmgt.poller.monitors.JDBCMonitor"/>
  <monitor service="MySQL-JDBC" class-name="org.opennms.netmgt.poller.monitors.JDBCMonitor"/>
  <monitor service="PostgreSQL-JDBC" class-name="org.opennms.netmgt.poller.monitors.JDBCMonitor"/>

Hats off to Jose for this work.