JDBC Collector

The JDBCCollector collects performance data via JDBC drivers. Attributes are retrieved using SQL queries. The JDBC driver for PostgreSQL is included with Horizon. Any other JDBC drivers must be manually loaded to collect from other types of database servers (see Install JDBC driver on OpenNMS Horizon Core and Install JDBC driver on Minion).

Collector facts

Class Name

org.opennms.netmgt.collectd.JdbcCollector

Package

core

Supported on Minion

Yes (see limitations)

Configuration Files

$OPENNMS_HOME/etc/jdbc-datacollection-config.xml

Limitations on Minion

No JDBC drivers are included with Minion. To install a JDBC driver, follow the steps in the Install JDBC driver on Minion page.

When running on Minion, you also cannot reference the data sources in opennms-datasources.xml. Instead, you must use the service parameters to set the JDBC connection settings.

Configuration and use

Table 1. Collector-specific parameters for the JdbcCollector
Parameter	Description	Default
Required
collection	The name of the JDBC collection to use.
Optional
data-source	An existing datasource defined in `opennms-datasources.xml`.	NO_DATASOURCE_FOUND
driver	Driver class name.	`org.postgresql.Driver`
url	JDBC URL.	jdbc:postgresql://OPENNMS_JDBC_HOSTNAME/opennms
user	JDBC username.	postgres
password	JDBC password.

JDBC collection configuration

Understanding resource types helps when editing collector-specific configuration files.

JDBC collections are defined in ${OPENNMS_HOME}/etc/jdbc-datacollection-config.xml.

This snippet provides a sample collection definition named opennms-stats:

<jdbc-collection name="opennms-stats">
  <rrd step="300">
    <rra>RRA:AVERAGE:0.5:1:2016</rra>
    <rra>RRA:AVERAGE:0.5:12:1488</rra>
    <rra>RRA:AVERAGE:0.5:288:366</rra>
    <rra>RRA:MAX:0.5:288:366</rra>
    <rra>RRA:MIN:0.5:288:366</rra>
  </rrd>
  <queries>
    <query name="opennmsQuery" ifType="ignore">
      <statement data-source="opennms">
        <queryString>select count(*) as event_count from events;</queryString>
      </statement>
      <columns>
        <column name="event_count" data-source-name="event_count" alias="event_count" type="GAUGE"/>
      </columns>
    </query>
  </queries>
</jdbc-collection>

Once the definition is added to ${OPENNMS_HOME}/etc/jdbc-datacollection-config.xml, you can test it using the collect command in the Karaf Shell:

opennms:collect org.opennms.netmgt.collectd.JdbcCollector 127.0.0.1 collection=opennms-stats data-source=opennms

To test this same collection on Minion, you must specify the JDBC settings as service attributes; for example:

opennms:collect -l MINION org.opennms.netmgt.collectd.JdbcCollector 127.0.0.1 collection=opennms-stats driver=org.postgresql.Driver url=jdbc:postgresql://localhost:5432/opennms user=opennms password=opennms