From OpenNMS

Contents 1 Running SQL Queries as a Monitored Service 1.1 Prereqs 1.2 Configuration Basics 1.3 Configuration Example 1.3.1 capsd-configuration.xml 1.3.2 poller-configuration.xml 1.3.2.1 MySQL Check 1.3.2.2 Oracle Check 1.3.2.3 Monitor to Service Mapping 1.4 Limitations

Running SQL Queries as a Monitored Service

The JDBCQueryMonitor allows you to run queries against your JDBC accessible databases and perform a minimal set of comparisons on the returned data.

Prereqs

You will (at present) need to be running trunk. In this case I will be showing MySQL and Oracle examples, but any supported JDBC database should work.

Configuration Basics

The JDBCQueryMonitor is just an extension of the Current JDBCMonitor. All the configuration parameters that are valid for it, are also valid for the JDBCQueryMonitor. The JDBCQueryMonitor add's the following parameters:

• query - the SQL query to run against the JDBC Connection
• action - what to do with the returned results from the query. You can only operator with the first row or the count of rows returned.
  • row_count
  • compare_string
  • compare_int
  • compare_bool
• operator - the comparison action for the value of the field or row count returned by the query
  • =
  • <
  • >
  •  !=
  • <=
  • >=
• column - If you are using one of the compare_ actions this will let you choose the column to run the operator against.
• operand - the value for the operator to use
• message - set a custom error message for the check failing, instead of the default.

Configuration Example

capsd-configuration.xml

  <protocol-plugin protocol="oracle-table" class-name="org.opennms.netmgt.capsd.plugins.JDBCQueryPlugin" scan="on" user-defined="false">
    <property key="user" value="username" />
    <property key="password" value="password" />
    <property key="driver" value="oracle.jdbc.driver.OracleDriver" />
    <property key="retry" value="1" />
    <property key="timeout" value="5000" />
    <property key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521/dbname" />
    <property key="query" value="SELECT created_date FROM table WHERE table_id = (SELECT MAX(table_id) FROM table)" />
  </protocol-plugin>

<protocol-plugin protocol="mysql-users"
    class-name="org.opennms.netmgt.capsd.plugins.JDBCQueryPlugin"
    scan="on" user-defined="false">
    <property key="query" value="SELECT username FROM Users WHERE user_id=1" />
    <property key="retry" value="1" />
    <property key="timeout" value="5000" />
    <property key="port" value="3306" />
    <property key="driver" value="com.mysql.jdbc.Driver" />
    <property key="url" value="jdbc:mysql://OPENNMS_JDBC_HOSTNAME:3306/databasename" />
    <property key="user" value="username" />
    <property key="password" value="password" />    
	 <protocol-configuration scan="off" user-defined="false">
            <range begin="0.0.0.0" end="10.0.0.0" />
            <range begin="10.255.255.255" end="255.255.255.255" />
     </protocol-configuration>
</protocol-plugin>

poller-configuration.xml

MySQL Check

<service name="mysql-users" interval="300000" user-defined="false" status="on">
	    <parameter key="query" value="SELECT username FROM Users WHERE user_id=1" />
	    <parameter key="retry" value="1" />
	    <parameter key="timeout" value="5000" />
	    <parameter key="port" value="3306" />
	    <parameter key="driver" value="com.mysql.jdbc.Driver" />
	    <parameter key="url" value="jdbc:mysql://OPENNMS_JDBC_HOSTNAME:3306/databasename" />
	    <parameter key="user" value="username" />
	    <parameter key="password" value="password" />
	    <parameter key="operator" value="=" />
	    <parameter key="action" value="compare_string" />
	    <parameter key="operand" value="foo" />
	    <parameter key="column" value="username" />
</service>

Oracle Check

 
<service name="oracle-table" status="on" user-defined="false" interval="300000">
    <parameter key="user" value="username" />
    <parameter key="password" value="password" />
    <parameter key="driver" value="oracle.jdbc.driver.OracleDriver" />
    <parameter key="retry" value="1" />
    <parameter key="timeout" value="5000" />
    <parameter key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521/database" />
    <parameter key="query" value="select created_date FROM table WHERE table_id = (SELECT MAX(table_id) FROM tables) AND to_char(created_date,'DD-MON-YYYY')=to_char(current_date,'DD-MON-YYYY')" />
  </service>

Monitor to Service Mapping

  <monitor service="mysql-users" class-name="org.opennms.netmgt.poller.monitors.JDBCQueryMonitor" />    
  <monitor service="oracle-table" class-name="org.opennms.netmgt.poller.monitors.JDBCQueryMonitor" />    

Limitations