OpenNMS configuration to monitor user tablespace quota usage in an Oracle database

capsd-configuration.xml

<protocol-plugin protocol="OracleMonitoring" class-name="org.opennms.netmgt.capsd.plugins.JDBCPlugin" scan="on">
    <property key="driver" value="oracle.jdbc.driver.OracleDriver"/>
    <property key="user" value="opennms"/>
    <property key="password" value="opennms"/>
    <property key="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OPENNMS_JDBC_HOSTNAME)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))"/>
    <property key="retry" value="1"/>
</protocol-plugin>

collectd-configuration.xml

<!-- add this under the default package (example1) -->
  <service name="OracleMonitoring" interval="600000" user-defined="false" status="on">
    <parameter key="collection" value="OracleMonitoring"/>
    <parameter key="thresholding-enabled" value="true"/>
    <parameter key="driver" value="oracle.jdbc.driver.OracleDriver"/>
    <parameter key="user" value="opennms"/>
    <parameter key="password" value="opennms"/>
    <parameter key="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OPENNMS_JDBC_HOSTNAME)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))"/>
  </service>
        
<!-- do not forget this at the bottom of the file -->
<collector service="OracleMonitoring" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>

datacollection-config.xml

<resourceType name="oracleQuota" label="Oracle Quota"
       resourceLabel="Account ${UserName} (index:${index})">
  <persistenceSelectorStrategy
       class="org.opennms.netmgt.collectd.PersistAllSelectorStrategy"/>
  <storageStrategy
       class="org.opennms.netmgt.dao.support.IndexStorageStrategy"/>
</resourceType>

jdbc-datacollection-config.xml

<?xml version="1.0"?>
<jdbc-datacollection-config rrdRepository="/opt/opennms/share/rrd/snmp/" xmlns="http://xmlns.opennms.org/xsd/config/jdbc-datacollection">
<!-- mysql data collection removed for brevity -->
    <jdbc-collection name="OracleMonitoring">
        <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="oracleQuota" ifType="ignore" instance-column="USERNAME" resourceType="oracleQuota">
          <statement>
            <queryString>
select
           username||'.'||tablespace_name as username,
           bytes,
           max_bytes
from
         dba_ts_quotas
where
          max_bytes > 0
            </queryString>
          </statement>
          <columns>
            <column name="USERNAME" alias="UserName" data-source-name="UserName" type="string"/>
            <column name="BYTES" alias="BytesUsed" data-source-name="BytesUsed" type="gauge"/>
            <column name="MAX_BYTES" alias="MaxBytes" data-source-name="MaxBytes" type="gauge"/>
          </columns>
        </query>
      </queries>
    </jdbc-collection>

</jdbc-datacollection-config>

snmp-graph.properties

# do not forget to add this graph to the report list (reports property) at the beginning of the file

report.oracleQuota.name=Oracle Tablespace Quotas
report.oracleQuota.columns=BytesUsed,MaxBytes
report.oracleQuota.propertiesValues=UserName
report.oracleQuota.type=oracleQuota
report.oracleQuota.command=--title="Oracle Tablespace Quota {UserName}" \
 --vertical-label="Usage" \
 DEF:a={rrd1}:BytesUsed:AVERAGE \
 DEF:b={rrd2}:MaxBytes:AVERAGE \
 AREA:b#00ddcc:" max" \
 AREA:a#0000cc:" used" \
 GPRINT:a:AVERAGE:"Avg: %8.2lf %s" \
 GPRINT:b:MIN:"Min: %8.2lf %s" \
 GPRINT:b:MAX:"Max: %8.2lf %s\\n" 

threshd-configuration.xml

<package name="oracle">
    <filter>IPADDR != '0.0.0.0'</filter>
    <include-range begin="1.1.1.1" end="254.254.254.254"/>
    <service name="OracleMonitoring" interval="300000" user-defined="false" status="on">
        <parameter key="thresholding-group" value="oracle"/>
    </service>
</package>

thresholds.xml

<group name="oracle" rrdRepository="/opt/opennms/share/rrd/snmp/">
    <expression type="high" ds-type="oracleQuota" value="90.0" rearm="85.0" ds-label="UserName"
        trigger="2" filterOperator="or" expression="BytesUsed / MaxBytes * 100.0"/>
</group>