JDBC Source

The JDBC source provides the ability to run an SQL Query against an external system and map the result to an OpenNMS requisition.

Parameter Required Description

source

*

Set jdbc to use this source for the requisition configuration

source.driver

*

JDBC driver, e.g. org.postgresql.Driver

source.url

JDBC URL, e.g. jdbc:postgresql://host:port/database

source.selectStatement

SQL statement

source.user

user name for database connection

source.password

password for database connection

mapper

*

Mapper script for changing the requisition. For no operation use echo; for a JSR-223 script set to script

mapper.file

If set mapper is set to script, relative path to your JSR-223 script for modifying the requisition

The following column-headers will be mapped from the result set to the OpenNMS requisition:

Column Header Required Description

Foreign_Id

*

will be interpreted as foreignId on the node

Parent_Foreign_Id

defines the foreignId for the parent node. Used for path outages.

Parent_Foreign_Source

defines the foreignSource for the parent node. Used for path outages.

Parent_Node_Label

defines the nodeLabel of the parent node. Used for path outages.

IP_Address

will be interpreted as an IP address for a new IP interface on the node

MgmtType

is interpreted as snmp-primary flag and controls how the interface can be used to communicate with the SNMP agent. Valid are P (Primary), S (Secondary) and N (None).

InterfaceStatus

will be interpreted as Interface Status. Value has to be an integer. Use 1 for monitored and 3 for not monitored.

Node_Label

will be interpreted as node label for the node identified by the Foreign_Id

Location

The monitoring location for the node. When not set, the node is monitored from the OpenNMS server, otherwise from the Minion associated with the Location.

Cat

will be interpreted as a surveillance category for the node identified by the Foreign_Id

Svc

will be interpreted as a service on the interface of the node identified by the Foreign_Id and IP_Address field

MetaData_

will be interpreted as node-level meta-data with the given key and the default context requisition. You can use MetaData_Context:Key to specify a custom context.
Please note, that this datasource only allows to specify node-level meta-data.

This source also supports all asset fields by using Asset_ as a prefix followed by the asset-field-name. The city field of the assets can be addressed like this: yourvalue AS Asset_City and is not case-sensitive.

Every row of the result set will be checked for the listed column headers. The provided data will be added to the corresponding node. Multiple result rows with matching Foreign_Id will be added to the same node.

To use additional JDBC drivers, just drop the JAR files into the opennms-pris/lib directory and set the source.driver and source.url accordingly.
Example configuration to import nodes from phpIPAM MySQL database into OpenNMS
### File: phpipam/requisition.properties
# This example connects to an phpIPAM MySQL database and imports the
# data and maps the result an OpenNMS requisition.
# Hint: the MySQL driver has to be manually installed to the opennms-pris/lib
# directory
source = jdbc

## jdbc source parameter to connect against phpIPAM on MySQL
source.driver = com.mysql.jdbc.Driver
source.url = jdbc:mysql://phpipam.foo.org/phpipam
source.user = user
source.password = secret

source.selectStatement = SELECT \
  id AS Foreign_Id, \
  dns_name AS Node_Label, \
  'P' AS MgmtType, \
  description AS Asset_Description, \
  INET_NTOA(ip_addr) AS Ip_Address, \
  owner AS Cat \
  FROM ipaddresses;

### default no-operation mapper
mapper = echo
Example configuration to import nodes from another OpenNMS database
### File: opennms/requisition.properties
# This example connects to an OpenNMS PostgreSQL database and imports the
# data and maps the result a new OpenNMS requisition
source = jdbc

## jdbc source parameter to connect against PostgreSQL
source.driver = org.postgresql.Driver
source.url = jdbc:postgresql://localhost:5432/opennms
source.user = opennms
source.password = opennms

source.selectStatement = SELECT \
  node.foreignId AS Foreign_Id, \
  node.nodelabel AS Node_Label, \
  node.location AS Location, \
  ipinterface.ipaddr AS IP_Address, \
  ipinterface.issnmpprimary AS MgmtType, \
  ipinterface.ipstatus AS InterfaceStatus, \
  assets.description AS Asset_Description, \
  assets.city AS Asset_City, \
  assets.state AS Asset_State, \
  service.servicename AS Svc, \
  categories.categoryname AS Cat \
FROM \
  node LEFT OUTER JOIN ipInterface ON node.nodeId=ipInterface.nodeId \
  LEFT OUTER JOIN ifServices ON ipInterface.id=ifServices.ipinterfaceid \
  LEFT OUTER JOIN service ON ifServices.serviceId=service.serviceId \
  LEFT OUTER JOIN category_node ON node.nodeId=category_node.nodeId \
  LEFT OUTER JOIN categories ON category_node.categoryId=categories.categoryId \
  LEFT OUTER JOIN assets ON node.nodeId=assets.nodeId;

### default no-operation mapper
mapper = echo