Skip to main content

Integrating openNMS to Confluence

15/12/2014 April 3rd, 2016

swissns GmbH logo-final copy

We are using heavily openNMS for the monitoring of our systems (and customers managed services) as well as Confluence for our internal documentation, procedures & policies.

We wanted to integrate some information from openNMS to our confluence pages, starting with “Current outages” which we wanted on our Confluence dashboard.

Step 1: Configure postgres

It is important to configure postgres on openNMS to accept connections from your confluence server. It is suggested to add a new user (as openNMS installs postgres with trust authentication).

If you need further information on postgres configuration please don’t hesitate to contact us.

Step 2: Install SQL add-on on Confluence. We used Bob Swift Atlassian Add-ons – SQL (Express Edition)

Step 3: Configure SQL add-on to point to your postgres server. An example is:

dbUrl=jdbc:postgresql://openNMS_server_IP:5432/opennms | dbUser=DBUSER | dbPassword=PASSWORD | dbDriver=org.postgresql.Driver | dbJar=http://jdbc.postgresql.org/download/postgresql-9.3-1102.jdbc41.jar

If you want to use mySQL an example is:

dbUrl=jdbc:mysql://openNMS_SERVER_IP:3306/confluence?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true | dbUser=DBUSER | dbPassword=PASSWORD | dbDriver=com.mysql.jdbc.Driver | dbJar=/opt/atlassian/confluence/lib/postgresql-9.2-1004.jdbc4.jar

Step 4: Add latest postgres JDBC on main confluence director lib (in our case /opt/atlassian/confluence/lib). We used postgresql-9.2-1004.jdbc4.jar

Step 5: Verify that the connection is working and add the SQL script on your Confluence page. The below examples will display nodes with outages.

UPDATE — With latest upgrade of openNMS the DB structure has changed and a new example of a working SQL script to query outages is:

SELECT
	node.nodelabel as "Node Label",
	ipinterface.ipaddr as "IP Address",
	service.servicename as "Service"
FROM 
	outages
	INNER JOIN ifservices ON (outages.ifserviceid = ifservices.id)
	INNER JOIN service ON (ifservices.serviceid = service.serviceid)
	INNER JOIN ipinterface ON (ipinterface.id = ifservices.ipinterfaceid)
	INNER JOIN node ON (node.nodeid = ipinterface.nodeid)
WHERE 
	outages.ifregainedservice is NULL
ORDER BY
  node.nodelabel ASC;

Example 1:

SELECT  
  outages.ipaddr as "IP Address",
  node.nodelabel as "Node Label",
  service.servicename as "Service"
FROM
  outages
  INNER JOIN node ON (outages.nodeid = node.nodeid)
  INNER JOIN service ON (service.serviceid = outages.serviceid)
WHERE
  outages.ifregainedservice IS NULL
ORDER BY
  node.nodelabel ASC;

Example 2:

SELECT
  outages.nodeid,
  outages.ipaddr,
  outages.iflostservice,
  node.nodelabel,
  node.nodeid,
  service.servicename,
  outages.svclosteventid AS eventid,
  notifications.notifyid,
  notifications.answeredby
FROM
  outages
  INNER JOIN node ON (outages.nodeid = node.nodeid)
  INNER JOIN service ON (service.serviceid = outages.serviceid)
  INNER JOIN notifications ON (outages.svclosteventid = notifications.eventid)
  INNER JOIN events ON (outages.svclosteventid = events.eventid)
WHERE
  outages.ifregainedservice IS NULL AND
  outages.suppresstime IS NULL
ORDER BY
  outages.nodeid,
  outages.iflostservice ASC;
Close Menu