From OpenNMS

Contents 1 Summary 2 Prerequisites 3 Enabling the JDBCCollector 3.1 Patching JdbcCollector for pre-5.1-MySQL versions 4 Configuring collectd 4.1 Allowing access to the monitoring user 5 Configuration of jdbc-datacollection and snmp graphs

Summary

This document is intended to provide an example of how to configure JDBC Data Collection.

Prerequisites

You will need the source code of opennms to compile this feature.

Enabling the JDBCCollector

First, get the Source code and compile it:

 wget 'http://sourceforge.net/projects/opennms/files/OpenNMS-Source/stable-1.8.3/opennms-source-1.8.3-1.tar.gz/download'

 tar -xvzf opennms-source-1.8.3-1.tar.gz

cd opennms-1.8.3-1/source

Patching JdbcCollector for pre-5.1-MySQL versions

Older MySQL versions do not have an information table for key performance variables, they use variables instead. In the "clean" source code, I was not able to collect more than one variable in this way (the column name is always "Value", resulting in the same jrb file being written to for each value).

For newer versions, you can change the configuration to a table query, having unique column names for each variable. In such a configuration, the patch below is not needed.

For patch, see below or [1]

src/opennms-1.8.3-1/source/features/jdbc-collector/src/main/java/org/opennms/netmgt/collectd# diff -c JdbcCollector.java.orig JdbcCollector.java

*** JdbcCollector.java.orig     2010-09-13 10:47:48.000000000 +0200

--- JdbcCollector.java  2010-09-13 10:51:46.000000000 +0200

***************

*** 60,66 ****

              for (JdbcColumn column : query.getJdbcColumns()) {

                  AttributeGroupType attribGroupType = m_groupTypeList.get(query.getQueryName());

                  JdbcCollectionAttributeType attribType = new JdbcCollectionAttributeType(column, attribGroupType);

!                 m_attribTypeList.put(column.getColumnName(), attribType);

              }

          }

      }

--- 60,72 ----

              for (JdbcColumn column : query.getJdbcColumns()) {

                  AttributeGroupType attribGroupType = m_groupTypeList.get(query.getQueryName());

                  JdbcCollectionAttributeType attribType = new JdbcCollectionAttributeType(column, attribGroupType);

!                 String columnName = null;

!                 if(column.getDataSourceName() != null && column.getDataSourceName().length() != 0) {

!                     columnName = column.getDataSourceName();

!                 } else {

!                     columnName = column.getColumnName();

!                 }

!                 m_attribTypeList.put(columnName, attribType);

              }

          }

      }

***************

*** 265,271 ****

                                      columnName = curColumn.getColumnName();

                                  }

!                                 JdbcCollectionAttributeType attribType = m_attribTypeList.get(curColumn.getColumnName());

                                  resource.setAttributeValue(attribType, results.getString(curColumn.getColumnName()));

                              }

--- 271,277 ----

                                      columnName = curColumn.getColumnName();

                                  }

!                                 JdbcCollectionAttributeType attribType = m_attribTypeList.get(columnName);

                                  resource.setAttributeValue(attribType, results.getString(curColumn.getColumnName()));

                              }

Build opennms to get the needed jar:

./build.sh install assembly:directory-inline

This will, among others, create

features/jdbc-collector/target/org.opennms.features.jdbc-collector-1.8.3.jar

, this sould be copied to

/usr/share/opennms/lib

. This is the path under Debian, it might vary for others.

Configuring collectd

collectd has to be configured for the JdbcCollector by adding a service element as following. Please make sure that the capsd-configuration is able to discover the service MySQL and that the values for user and password match your environment. Do NOT change the url parameter.

 <service name="MySQL" interval="300000" user-defined="false" status="on">
   <parameter key="retry" value="2"/>
   <parameter key="timeout" value="3000"/>
   <parameter key="collection" value="mysql"/>
   <parameter key="url" value="jdbc:mysql://OPENNMS_JDBC_HOSTNAME/information_schema"/>
   <parameter key="user" value="opennms"/>
   <parameter key="password" value="<password>"/>
   <parameter key="driver" value="com.mysql.jdbc.Driver"/>
 </service>
 [...]
 <collector service="MySQL" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>

Allowing access to the monitoring user

For MySQL, the following enables the monitoring user to read the status variables:

  grant usage on *.* to 'opennms'@'%' identified by '<password>';

In case you locked the user, allow access to MySQL again on the database host by:

mysqladmin flush-hosts

Configuration of jdbc-datacollection and snmp graphs

Finally, the JdbcCollector and the graphs have to be configured. Please see the linked documents:

• JDBC Collection: configuration files for MySQL

--Michael 08:32, 31 October 2010 (EDT)