JDBC Data Collection Tutorial

From OpenNMS
Jump to: navigation, search

This tutorial intends to be a guide through the basic steps you should follow to retrieve data and generate graphical reports from the result of an SQL query.

Note.png Database Driver

Prior to any changes in configuration files and depending on which kind of database server are you interested to monitor, you need to find the proper JDBC Driver for that database and put the JAR in $OPENNMS_HOME/lib.


Suppose that you have a database named balances hosted on a MySQL Server, and you want to graph the number of accounts and the current balance per account.

Collection Package

The first thing you need to do is to create a package (or modify an existing one) inside $OPENNMS_HOME/etc/collectd-configuration.xml to add the service that will be associated with the JDBC collector.

The following is a sample of a new package with a custom filter to associate the collector with the monitored nodes:

<package name="databases">
  <filter>categoryName = 'Databases'</filter>      
  <include-range begin="1.1.1.1" end="254.254.254.254"/>
  <service name="MySQL-Balances" interval="300000" user-defined="false" status="on">
    <parameter key="collection" value="mysql-balances"/>
    <parameter key="thresholding-enabled" value="true"/>
    <parameter key="driver" value="com.mysql.jdbc.Driver"/>
    <parameter key="user" value="opennms"/>
    <parameter key="password" value="opennms"/>
    <parameter key="url" value="jdbc:mysql://OPENNMS_JDBC_HOSTNAME:3306/balances"/>
  </service>
</package>
<collector service="MySQL-Balances"
    class-name="org.opennms.netmgt.collectd.JdbcCollector"/>


This will associate the JDBC Collector to a service called MySQL-Balances on all nodes which belong to the surveillance category named Databases.

The following parameters are mandatory for this kind of collector:

  • driver, the driver to be used for this collector, in this case MySQL
  • user, the username of the account with access to the database balances
  • password, the password of the account with access to the database balances
  • url, the URL that points to the database balances
  • collection, the JDBC collection to be attached to the service

The keyword OPENNMS_JDBC_HOSTNAME used in the URL, will be replaced at runtime with the IP address of the node that will be used for data collection.

The parameter named collection is associated with the JDBC Collection name defined on $OPENNMS_HOME/etc/jdbc-datacollection-config.xml

JDBC Data Collection Config

For the tutorial, we want to retrieve the number of accounts and the balance of each account from the sample database. Here is the structure:

<jdbc-collection name="mysql-balances">
  <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="accountsQuery" ifType="ignore">
      <statement>
        <queryString>
            SELECT COUNT(*) AS accountsCount FROM accounts
        </queryString>
      </statement>
      <columns>
        <column name="accountsCount" data-source-name="accountsCount"
            alias="accountsCount" type="counter"/>
      </columns>
    </query>
    <query name="accountsBalance" ifType="ignore"
        instance-column="accountId" resourceType="balanceAccount">
      <statement>
        <queryString>
             SELECT accountId, accountName, accountBalance
               FROM accounts
        </queryString>
      </statement>
      <columns>
        <column name="accountName" data-source-name="accountName"
            alias="accountName" type="string"/>
        <column name="accountBalance" data-source-name="accountBalance"
            alias="accountBalance" type="gauge"/>
      </columns>
    </query>
  </queries>
</jdbc-collection>

There are two kinds of data here:

  • Node Data (to store a specific metric that will be stored at node's level, like the query named accountsQuery)
  • Table Data (to store the result of a table where each row will be treated as a resource in OpenNMS, like the query named accountsBalance)

Each column name contains 4 elements: the name and data-source-name should be the same and must match the column name of the database. The alias is the label to be used inside the RRDs for the column. The type of the numeric value (gauge or counter).

You must create the resource type named balanceAccount. In order to do that, we need to create a file named $OPENNMS_HOME/etc/datacollection/mysql-balances.xml, and put the following content inside:

<datacollection-group name="MySQL Balances">
    <resourceType name="balanceAccount" label="MySQL Balances"
           resourceLabel="Account ${accountName} (index:${index})">
      <persistenceSelectorStrategy
           class="org.opennms.netmgt.collectd.PersistAllSelectorStrategy"/>
      <storageStrategy
           class="org.opennms.netmgt.dao.support.IndexStorageStrategy"/>
    </resourceType>
</datacollection-group>

Because the SQL query produces multiple rows, we need to specify the column that must uniquely identify each row (or OpenNMS resource). For this case we can use any column that produce unique and fixed values every time we execute the query. As this is an SQL table, we should use the primary key of the accounts table named "accountId", and set the attribute “instance-column” inside the tag query with this name.

Service Detection

Use the JDBCDetector in the default foreign-source definition as shown below to make the MySQL-Balances service discoverable.

Mysql-balances-detector.png

Graph Templates

For 1.10

Create a file named $OPENNMS_HOME/etc/snmp-graph.properties.d/mysql-balances.properties, with the following content:

reports=mysql-balances.balance, mysql-balances.accounts

report.mysql-balances.balance.name=MySQL Balance
report.mysql-balances.balance.columns=accountBalance
report.mysql-balances.balance.propertiesValues=accountName
report.mysql-balances.balance.type=balanceAccount
report.mysql-balances.balance.command=--title="Account {accountName}" \
 --vertical-label="USD" \
 DEF:b={rrd1}:accountBalance:AVERAGE \
 AREA:b#0000cc:" balance" \
 GPRINT:b:AVERAGE:"Avg: %8.2lf %s" \
 GPRINT:b:MIN:"Min: %8.2lf %s" \
 GPRINT:b:MAX:"Max: %8.2lf %s\\n" 

report.mysql-balances.accounts.name=MySQL Accounts
report.mysql-balances.accounts.columns=accountsCount
report.mysql-balances.accounts.type=nodeSnmp
report.mysql-balances.accounts.command=--title="Number of Accounts" \
 --vertical-label="# of accounts" \
 DEF:b={rrd1}:accountsCount:AVERAGE \
 AREA:b#0000cc:" accounts" \
 GPRINT:b:AVERAGE:"Avg: %8.2lf %s" \
 GPRINT:b:MIN:"Min: %8.2lf %s" \
 GPRINT:b:MAX:"Max: %8.2lf %s\\n" 

For 1.8

Because 1.8 does not support the split feature in $OPENNMS_HOME/etc/snmp-graph.properties, you must include the templates defined at the end of this file, and then update the reports entry at the top of the file and add a reference to the new templates.

Restart OpenNMS

You should be able to access the database with the credentials provided in collectd-configuration.xml from the OpenNMS server.

After making changes in collectd-configuration.xml and/or jdbc-datacollection-config.xml you must restart OpenNMS.

Configure Nodes

Based on how Collectd has been configured for this particular tutorial, you need to assign the node (or nodes) with the Balance Database to a category named Databases. The service MySQL-Balances must be added to this node (or these nodes). Rescan the node(s) and the service will be discovered.