From OpenNMS

Contents 1 Situation 2 OpenNMS Database Configuration 3 Confluence Configuration 3.1 Tomcat Configuration 3.2 SQLPlugin 4 Accessing the DB via Confluence

Situation

We are "heavy" users of Atlassian's Confluence Wiki; it's the base for our documentation and "the" tool to get things organized.

As with all documentation systems we lack however (very rarely of course) the discipline to make sure that changes are really updated in time.

Where this considers the network we do however have a sound database of the current status - in OpenNMS.

So - how could we link confluence to OpenNMS?

OpenNMS Database Configuration

The first step is to make the Postgres behind OpenNMS speak to the outside world. This requires postgres configuration. Other people know better how to do this and I will not embarrass myself here ;-)

The optimal situation would be to use a read-only user for the database. In any case the db must be accessible via the network from the confluence server.

Confluence Configuration

Confluence is powered by Tomcat (I was hoping to not have to fight with it anymore but in fact I found the documentation to be very good).

The target is to make the OpenNMS Database available as a Datasource within Tomcat.

Tomcat Configuration

I found this (http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html) very helpful.

• First, copy the postgresql jdbc driver into the $CATALINA_HOME/common/lib directory

I found the driver with confluence (postgresql-8.1-408.jdbc3.jar)

• Then edit your server.xml to contain in the <Context>:

 <Context path="" docBase="/usr/local/apache-tomcat-5.5.20/confluence-2.8.1" debug="0" reloadable="true">
   <Resource name="jdbc/opennms" auth="Container" type="javax.sql.DataSource"
                        username="$YourUser"
                        password="$YourPassword"
                        driverClassName="org.postgresql.Driver"
                        url="jdbc:postgresql://$YourServer:5432/opennms?autoReconnect=true"
                        validationQuery="Select 1" />
   </Context>

($YourUser and $YourPassword as well as $YourServer need to match yours..)

If you restart Tomcat now it should feature the Datasource (which you will not see anywhere for now).

SQLPlugin

The Confluence SQL Plugin is documented here: http://confluence.atlassian.com/display/CONFEXT/SQL+Plugin I installed it using the Plugin Managament function in Confluence 2.8. You need and should configure as well the Plugin/Macro Security Model to make sure that only the people you trust play with your databases.

You might need to restart Confluence to have the plugin available.

Accessing the DB via Confluence

The DB is accessible via the network, tomcat has the postgres jdbc driver and confluence features the SQLPlugin.

Now you can edit a page and add SQL Queries:

{sql-query:dataSource=opennms|output=wiki}
 select nodelabel, nodesysdescription, description from node,category_node, categories, assets where 
 categories.categoryID = category_node.categoryID AND categories.categoryName = 'Production' AND node.nodeid = 
 category_node.nodeid AND assets.nodeid = node.nodeid ORDER BY nodelabel
{sql-query}

For the beauty I added line breaks in my SQL Query - the SQLPlugin needs the query on one line though. So if you copy/paste, remove the line breaks.

I suggest further to use the "wiki" editor and not the Wysiwyg editor. The wysiwyg escapes in an (for me) unpredictable fashion characters which might break the SQL Queries.

The above Query will show a list of the Nodes which I put in the Surveillance Category "Production", with their Nodelabel, the SNMP-collected system description and the description from the assets table.

My SQL is certainly improvable, but it works.