- 1 Workshop from the OUCE 2010
- 2 Reporting Cheat Sheet
Workshop from the OUCE 2010
I did a little workshop on OUCE2010 to create reports with JasperSofts iReport designer. To follow the workshop you have to fulfill the following requirements:
- OpenNMS 1.7.10 or later which delivers reportd and/or Report API
- iReport 3.6.0 (note that the latest version of iReport can include features not supported by the version of Jasper Reports used by OpenNMS. To check which version your OpenNMS install uses, look for the version number in OpenNMS' libs/jasperreport-<version>.jar (1.8.11 uses v3.7.6). To get old versions of iReport, visit  etc)
- Access from your iReport desktop to the OpenNMS database
- optional and helpful, a database client ppgadmin III
- OpenNMS DB schema
The goal of this tutorial answered the question: "How can I create a report for OpenNMS with almost no idea from iReport". From start to end I will describe the development in a few steps. The final report shows some asset informations, all outages with services and a event chart for each node. After this tutorial you'll be able to group data by a key with iReport´s Report Groups, the usage of datasets for charts and the usage of parameters from a main query to a more specific query in a dataset. Finally you'll be also able to add images as additional resources to your report.
Ok let´s start with our tutorial and have fun
P.S. I'm not a native english speaker, so sorry about that, but I'll do my best and ready to learn ;)
Content is available under a Creative Commons Attribution-NonCommercial-ShareAlike3.0 License.
Without content no report - SQL
To get the required informations we need an SQL statement based on the OpenNMS database. I´ve created a statement.
SELECT node.nodeid, node.nodelabel, node.nodesyscontact, service.servicename, node.nodetype, assets.city, assets.maintcontract, assets.serialnumber, assets.supportphone, assets.lease, assets.room, assets.assetnumber, assets.leaseexpires, assets.address1, assets.modelnumber FROM outages JOIN node ON (outages.nodeid=node.nodeid) JOIN service ON (outages.serviceid=service.serviceid) JOIN assets ON (node.nodeid=assets.nodeid) WHERE node.nodetype!='D'
The projection in the select gives us the necessary fields for the later use in our report. Hint: It make sense to format your SQL statements. It´s easy to extend columns, joins and where clauses. Statements become very complex – readability matters. A result in pgadmin looks like this
Hint: With the WHERE CLAUSE nodetype != 'D' all deleted nodes will be ignored in your report. OpenNMS set nodes first to 'D' and vacuumd deletes it after while from the database. The same with interfaces and services.
Create a database connection
Before we can start with designing a report, we need a OpenNMS database connection to retrieve the necessary data. We create a database connection to our OpenNMS database to make all data available during the report design. Follow the 8 steps from the screenshot below. My OpenNMS machine is named marge. My database is named opennms. The default password is also opennms. Hint: The default setup of postgres allows only connection from local loopback. Please check the two files
postgresql.conf listen_addresses = '*'
pg_hba.conf host all all <your-host-or-network-in-cidr> trust
A blank new report
Create a new file. First we will use no templates, just blank. Save the report as MyReport.jrxml in a location you can remember. The report itself should also named MyReport.
IMPORTANT: Change the MyReport property Language from Groovy to Java. You will get an error in the OpenNMS if you leave it on Groovy (Report Inspector --> right click MyReport --> Properties).
The next step is to add the SQL statement from 1.1.
Before we start designing our report, I will give you a short idea of the main structure of JasperReport. A report is divided into different parts called "band". Each band has a different usage. The default band with a short description:
- Title is the first page or a cover
- Page Header/Footer displayed in top/bottom of each page
- Column Header/Footer displayed in top/bottom of a column. Our report has only one column.
- Detail 1 a line from a result
- Summary the last page of a report
For our first report draft we use only:
- Page Header
- Detail 1
- Page Footer
It make it easier to understand the working of iReport. We will use later other bands.
Now we can design our first report. To do this we have to place data fields on the report. My report looks now like this:
I´ve added a page header and footer. The report execution time will be displayed. Hint: Avoid overlapping fields or report elements. Take a look at the color of your selectors. They should be NOT green.
Rule number one for each report. "Put your company logo on the report!" It raises the acceptance significantly :)
Ok this is an easy part. Drag & Drop an Image from your palette in the Page Header and choose a JPG from your disc. You see, the logo is added in original size. iReport 3.6.0 does not allow to resize with correct ratio. You´ve to create an image with correct size or calculate the correct ratio manually and add the correct values in the image "Properties". Hint: Remember the location, you need this file again. You´ve to copy the jpg file to your OpenNMS box.
Now the time is come to look at the first result. The "Preview" shows the report with the current data from your previously configured database connection. My report looks now like this:
Improvement: Create groups
To remove the duplicated entries for services we will create two report groups. A report group is comparable to a SQL group by expression. To use Report Groups you have to sort your SQL result set first. Right click MyReport --> Edit Query. Our report query looks now like this:
SELECT node.nodeid, node.nodelabel, node.nodesyscontact, service.servicename, node.nodetype, assets.city, assets.maintcontract, assets.serialnumber, assets.supportphone, assets.lease, assets.room, assets.assetnumber, assets.leaseexpires, assets.address1, assets.modelnumber FROM outages JOIN node ON (outages.nodeid=node.nodeid) JOIN service ON (outages.serviceid=service.serviceid) JOIN assets ON (node.nodeid=assets.nodeid) WHERE node.nodetype!='D' ORDER BY node.nodelabel ASC, service.servicename ASC
Now we add two new report groups see in the picture below.
For the service_group you have to select for "Group by the following report object:" the object servicename. Each group has a header and a footer. We use in our example only the header and footer for the node group and only the header band for the service group.
You see now a new band in your report designer. After creating the groups you have to move all node specific fields from the detail group to the node group. It´s easier to select all fields in the left Report Inspector window and drag&drop it from "Detail 1" to the new node_group. With this method you don't have to replace all fields.
We add a new field from the Report Inspector to count outages for each service. The variable will be automatically created for each new created group. You will find the function variable in
Variables --> service_group_COUNT
Hint: You can add also customized function variables with SUM, AVG instead of COUNT. After positioning the function variable in the service_group header we have to verify two settings in the Properties window. Select the function variable and set the property Evaluation Time to Group and the Evaluation group to service_group. This settings modify the counter reset behavior.
In the next step we will enhance our report with adding a event histogram as a bar chart for each node.
Datasets and Charts
The next step to create a event histogram, we create a dataset based bar chart. A dataset is specialized SQL statement and gives another result set. We can use this data and represent this informations in a bar chart. First we need a new SQL statement.
SELECT node_events.nodeid, node_events.nodelabel, SUM(indeterminate) AS indeterminate, SUM(cleared) AS cleared, SUM(normal) AS normal, SUM(warning) AS warning, SUM(minor) AS minor, SUM(major) AS major, SUM(critical) AS critical, SUM(indeterminate + cleared + normal + warning + minor + major + critical) AS total FROM (SELECT node.nodeid, node.nodelabel, CASE WHEN events.eventseverity = '1' THEN CAST('1' AS INTEGER) ELSE CAST('0' AS INTEGER) END AS indeterminate, CASE WHEN events.eventseverity = '2' THEN CAST('1' AS INTEGER) ELSE CAST('0' AS INTEGER) END AS cleared, CASE WHEN events.eventseverity = '3' THEN CAST('1' AS INTEGER) ELSE CAST('0' AS INTEGER) END AS normal, CASE WHEN events.eventseverity = '4' THEN CAST('1' AS INTEGER) ELSE CAST('0' AS INTEGER) END AS warning, CASE WHEN events.eventseverity = '5' THEN CAST('1' AS INTEGER) ELSE CAST('0' AS INTEGER) END AS minor, CASE WHEN events.eventseverity = '6' THEN CAST('1' AS INTEGER) ELSE CAST('0' AS INTEGER) END AS major, CASE WHEN events.eventseverity = '7' THEN CAST('1' AS INTEGER) ELSE CAST('0' AS INTEGER) END AS critical FROM node JOIN events ON (events.nodeid = node.nodeid) WHERE node.nodetype!='D') AS node_events GROUP BY node_events.nodeid, node_events.nodelabel
The result set as chart data looks like this:
Now we can add a new dataset with right click on MyReport in Report Inspector followed by Add Dataset. In properties we change the dataset name to event_chart. We add our SQL statement with right click on event_chart and Edit Query. In a next step we can create a bar chart from the Palette window. To assign the dataset to the chart do a right click on the chart and select Chart Data and choose event_chart for the Sub dataset.
The next step is to configure the chart itself. It is necessary to define which data will be represented. I decided to display the event categories on the X-axis and the event count on the Y-Axis.
If run the report with this configuration we will see, for each node a event histogram over all nodes. The SQL statement displays a summary see the pgadmin output above. To create a chart for a specific node we have to add a parameter to the dataset. The dataset will then be parametrized for the specific node. As parameter we choose the unique node.nodeid. We create a parameter in the dataset and change the name from parameter1 into node_id. A nodeid is an Integer so we change the Parameter Class to java.lang.Integer. You can deselect Use as a prompt and we have to assign a Default Value Expression in case the parameter is empty what never should happen. Set the Default Value Expression to new Integer("1"). Hint: If you not add a default value expression you can't use the parameter in the query. iReport gives you a warning.
Now we have to add the created parameter in our dataset SQL query. Right click on event_chart dataset and select Edit Query. Change the query as follow:
We pick with the node id as parameter only the node specific row from our generic event chart SQL query. It is easier to identify the events by color if we use the same color as in OpenNMS. We change the property Series Colors from the chart. The changes are shown in the picture below.
An example of our created report is shown as follows. We are now ready to get this report to our OpenNMS box and make it alive. The next steps show you the deployment in Report API and the Reportd feature which is available in OpenNMS 1.7.10 later and the new stable 1.8.
Deploy the report to OpenNMS
Please verify the report can be executed in iReport by hit the Preview button. Our current result looks like this:
For both versions we have to copy the JRXML file to our OpenNMS box. Use scp or a tool you prefer to transfer the file the following directory:
You have also to copy your logo image into the same directory. In our example
Now we can configure OpenNMS to use our new created report.
IMPORTANT: Before we start with the configuration you have to change the path to your logo in the report. Open the MyReport.jrxml with an editor of your choice and search for the following line:
and change the absolute path to your logo image.
Method 1: Configure feature reportd
Reportd is a dedicated daemon or service in OpenNMS and run scheduled reports. It doesn't allow on-demand access to the report. Reportd execute the report and mail to a mailbox or store the report as pdf in the filesystem (or both). First check if the Reportd service is enabled in
$OPENNMS_HOME/etc/service-configuration The following entry should be uncommented: <service> <name>OpenNMS:Name=Reportd</name> <class-name>org.opennms.netmgt.daemon.SimpleSpringContextJmxServiceDaemon</class-name> <attribute> <name>LoggingPrefix</name> <value type="java.lang.String">Reportd</value> </attribute> <attribute> <name>SpringContext</name> <value type="java.lang.String">reportdContext</value> </attribute> <invoke at="start" pass="0" method="init"/> <invoke at="start" pass="1" method="start"/> <invoke at="status" pass="0" method="status"/> <invoke at="stop" pass="0" method="stop"/> </service>
To bring the MyReport into reportd we have to add following lines to
$OPENNMS_HOME/etc/reportd-configuration.xml <?xml version="1.0" encoding="UTF-8"?> <reportd-configuration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.opennms.org/xsd/config/reportd-configuration" storage-location="/opt/opennms/share/reports/" persist-reports="yes" > <!-- http://quartz.sourceforge.net/javadoc/org/quartz/CronTrigger.html Field Name Allowed Values Allowed Special Characters Seconds 0-59 , - * / Minutes 0-59 , - * / Hours 0-23 , - * / Day-of-month 1-31 , - * ? / L W C Month 1-12 or JAN-DEC , - * / Day-of-Week 1-7 or SUN-SAT , - * ? / L C # Year (Opt) empty, 1970-2099 , - * / --> <report report-name="sample-report" report-template="sample-report.jrxml" report-engine="jdbc"> <cron-schedule>0 0 0 * * ? *</cron-schedule> </report> <report report-name="Early-Morning-Report" report-template="Early-Morning-Report.jrxml" report-engine="jdbc"> <cron-schedule>0 0 0 * * ? *</cron-schedule> <!-- daily, at midnight --> <recipient>firstname.lastname@example.org</recipient> </report> <report report-name="MyReport" report-template="MyReport.jrxml" report-engine="jdbc"> <cron-schedule>0 0 0 * * ? *</cron-schedule> <!-- daily, at midnight --> <recipient>email@example.com</recipient> </report> </reportd-configuration>
You have to restart OpenNMS to load the new configuration in Reportd. The report will be executed every midnight and a PDF is stored in the filesystem and reportd sends an email to my mailbox.
Hint: The attribute report-name must set to the name of the report:
Method 2: Configure feature report-api
To allow on-demand reporting and additional scheduling from the WebUI you have to modify two files:
$OPENNMS_HOME/etc/jasper-reports.xml <?xml version="1.0" encoding="UTF-8"?> <jasper-reports xmlns:this="http://xmlns.opennms.org/xsd/config/jasper-reports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.opennms.org/xsd/config/jasper-reports"> <report id="sample-report" template="sample-report.jrxml" engine="jdbc" /> <report id="trivial-report" template="trivial-report.jrxml" engine="null" /> <report id="Early-Morning-Report" template="Early-Morning-Report.jrxml" engine="jdbc" /> <report id="MyReport" template="MyReport.jrxml" engine="jdbc" /> </jasper-reports>
$OPENNMS_HOME/etc/database-reports.xml <?xml version="1.0" encoding="UTF-8"?> <database-reports xmlns:this="http://xmlns.opennms.org/xsd/config/opennms-database-reports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.opennms.org/xsd/config/opennms-database-reports"> <report id="defaultCalendarReport" display-name="default calendar report" report-service="availabilityReportService" description="standard opennms report in calendar format" /> <report id="defaultClassicReport" display-name="default classic report" report-service="availabilityReportService" description="standard opennms report in tabular format" /> <report id="sample-report" display-name="sample JasperReport" online="true" report-service="jasperReportService" description="sample Jasper report using jdbc datasource" /> <report id="trivial-report" display-name="trivial JasperReport" online="true" report-service="jasperReportService" description="trivial Jasper report using null datasource" /> <report id="Early-Morning-Report" display-name="Early morning report" online="true" report-service="jasperReportService" description="Global overview of outages, notifications and events in last 24 hours" /> <report id="MyReport" display-name="My Report from OUCE2010" online="true" report-service="jasperReportService" description="The report from the workshop from OpenNMS User Conference 2010." /> </database-reports>
For Report-API it is not necessary to restart OpenNMS. The report is directly available in the OpenNMS WebUI.
Reporting Cheat Sheet
For a summary I have created a Reporting Cheat Sheet. It contains the steps to configure Reportd and Report-API. There are also a few SQL statement templates.
You can download all created step-by-step Report.jrxml files from here
--_indigo 23:28, 14 June 2010 (UTC)