From OpenNMS

Contents 1 Using JDBC and OpenNMS to Monitor Microsoft SQL 1.1 Install the MS SQL Driver 1.2 Connection Strings 1.3 Driver 1.4 Schema 1.5 Example Configurations 1.6 Additional Reading

Using JDBC and OpenNMS to Monitor Microsoft SQL

A few small changes are needed to utilize the JDBC Plugin to monitor Microsoft SQL Servers. The JDBC Monitor will request a listing of databases from the SQL instance to check it's availability, or run a stored procedure and check it for PASS/FAIL status.

Note regarding Authentication: You must use SQL Authentication. Integrated Windows Authentication is not supported at this time.

Install the MS SQL Driver

The first thing needed is the JDBC driver for Microsoft SQL.

• http://www.microsoft.com/downloads/details.aspx?FamilyId=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en
• Extract sqljdbc.jar to $OPENNMS_HOME/lib/

OpenNMS will need to be restarted to recognized the new jar file.

Connection Strings

A couple configuration lines in poller and capsd configurations will need to be adjusted as well.

Database Servers without instances

• jdbc:sqlserver://servername:1433;databaseName=dbname

Database Servers with multiple instances

• jdbc:sqlserver://servername\\instance:1433;databasename=dbname

Driver

• com.microsoft.sqlserver.jdbc.SQLServerDriver

Schema

• dbo

Example Configurations

poller-configuration.xml

<service name="MsSqlDbPoller" interval="300000" user-defined="false" status="on">
         <parameter key="retry" value="3"/>
         <parameter key="timeout" value="3000"/>
         <parameter key="user" value="username"/>
         <parameter key="password" value="password"/>
         <parameter key="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
         <parameter key="url" value="jdbc:sqlserver://servername:1433;databaseName=dbname"/>
         <parameter key="rrd-repository" value="/var/lib/opennms/rrd/response"/>
         <parameter key="ds-name" value="MsSql-DbPoller"/>
     </service>

<monitor service="MsSqlDbPoller" class-name="org.opennms.netmgt.poller.monitors.JDBCMonitor"/>

capsd-configuration.xml

<protocol-plugin protocol="MsSqlDbPoller" class-name="org.opennms.netmgt.capsd.plugins.JDBCPlugin" scan="on">
         <property key="user" value="username"/>
         <property key="password" value="password"/>
         <property key="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
         <property key="url" value="jdbc:sqlserver://servername:1433;databaseName=dbname"/>
     </protocol-plugin>

Additional Reading

• JDBC stored procedure monitor
• JDBC Monitor