Database Reports

Reporting on information from the Horizon monitoring system is important for strategical or operational decisions. Database reports give access to the embedded JasperReports engine and allow users to create and customize report templates. Run these reports on demand or on a predefined schedule within Horizon.

Originally database reports created reports working on data stored in the Horizon database only. This is no longer mandatory, you can also use performance data. Theoretically, the reports do not need to be Horizon related.

The Horizon Report Engine lets you create reports and supports distributed report repositories. This documentation does not cover these features. It describes only reports using JasperReports and Grafana dashboards.

Overview

The Horizon Report Engine uses the JasperReport library to create reports in various output formats. Each report template must be a *.jrxml file. The Horizon Report Engine passes a JDBC connection to the Horizon database to each report on execution.

Table 1. Feature overview
Supported Output Formats	PDF, CSV
JasperReport Version	6.3.0

For more details on how JasperReports works, please refer to the Jaspersoft Studio official documentation.

Modify existing reports

All default Horizon reports are located in $OPENNMS_HOME/etc/report-templates. Each .jrxml file located there can be modified; the changes are applied the next time Horizon creates a report.

When a subreport has been modified, Horizon will detect a change based on the report’s lastModified time and will recompile the report. A compiled version of the report is represented by a .jasper file with the same name as the .jrxml file. Subreports are located in $OPENNMS_HOME/etc/report-templates/subreports.

If unsure, simply delete all .jasper files and Horizon will automatically compile the subreports if needed.

Add a custom report

To add a new JasperReport report to the local Horizon report repository, do the following:

Create a new entry in the $OPENNMS_HOME/etc/database-reports.xml file.

<report
  id="MyReport" (1)
  display-name="My Report" (2)
  online="true" (3)
  report-service="jasperReportService" (4)
  description="This is an example description. It shows up in the web ui when creating an online report"  (5)
/>

1	A unique identifier
2	The name of the report. Appears in the web UI.
3	Defines if this report can be executed on demand, otherwise only scheduling is possible.
4	The report service implementation to use. In most cases this is `jasperReportService`.
5	A description of the report. Appears in the web UI.

In addition, create a new entry in the $OPENNMS_HOME/etc/jasper-reports.xml file.

<report
  id="MyReport" (1)
  template="My-Report.jrxml" (2)
  engine="jdbc" (3)
/>

1	The identifier defined in the previous step. This identifier must exist in `$OPENNMS_HOME/etc/database-reports.xml`.
2	The name of the template. The template must be located in `$OPENNMS_HOME/etc/report-templates`.
3	The engine to use. It is either `jdbc` or `null`.

Jaspersoft Studio use

When developing new reports, we recommended using the Jaspersoft Studio application. Download it here.

We recommend to always use the same Jaspersoft Studio version that the Horizon JasperReport library uses. Currently Horizon uses version 6.3.0.

Connect to the Horizon database

To actually create SQL statements against the Horizon database you must create a database Data Adapter. The official Jaspersoft Studio documentation and wiki describe how to do this.

Use Measurements Datasource and Helpers

To use the Measurements API you must add the Measurements Datasource library to the build path of JasperStudio. To do so, right click in the Project Explorer and select Configure Buildpath.

Switch to the Libraries tab.
Click Add External JARs and select the opennms-jasperstudio-extension-{opennms-version}-jar-with-dependencies.jar file located in $OPENNMS_HOME/contrib/jasperstudio-extension.
Close the file selection dialog.
The Measurements Datasource and Helpers should now be available.
Go to the Dataset and Query Dialog in Jaspersoft Studio and select the "measurement" language.

If the Read Fields functionality is not available, use the Data preview. Use the MEASUREMENT_URL, MEASUREMENT_USERNAME, and MEASUREMENT_PASSWORD connection parameters to Access the Measurements API. See Supported Fields for more details.

Accessing performance data

Before OpenNMS Horizon 17 and OpenNMS Meridian 2016, you could access the performance data stored in .rrd or .jrobin files directly by using the jrobin language extension the RrdDataSource provides. This is no longer possible; you must use the Measurements Datasource.

To access performance data within reports, we created a custom Measurement Datasource that lets you query the Measurements API and process the returned data in your reports. Please refer to the official Measurements API documentation on how to use the Measurements API.

When using the Measurements Datasource within a report, an HTTP connection to the Measurements API is established only if the report is not running within Horizon; for example, when used with Jaspersoft Studio.

To receive data from the Measurements API, create a query as follows:

Sample queryString to receive data from the Measurements API

<query-request step="300000" start="$P{startDateTime}" end="$P{endDateTime}" maxrows="2000"> (1)
  <source aggregation="AVERAGE" label="IfInOctets" attribute="ifHCInOctets" transient="false" resourceId="node[$P\{nodeid}].interfaceSnmp[$P{interface}]"/>
  <source aggregation="AVERAGE" label="IfOutOctets" attribute="ifHCOutOctets" transient="false" resourceId="node[$P\{nodeid}].interfaceSnmp[$P{interface}]"/>
</query-request>

1	The query language. In our case, "measurement", but JasperReports supports a lot out of the box, such as sql, xpath, and so on.

Fields

Each data source should return a number of fields, which you can use in the report. The Measurement Datasource supports the following fields:

Name Description Type

Name	Description	Type
label	Each source defined as `transient=false` can be used as a field. The name of the field is the label; for example, `IfInOctets`.	java.lang.Double
timestamp	The timestamp of the sample.	java.util.Date
step	The step size of the response. Returns the same value for all rows.	java.lang.Long
start	The start timestamp in milliseconds of the response. Returns the same value for all rows.	java.lang.Long
end	The end timestamp in milliseconds of the response. Returns the same value for all rows.	java.lang.Long

label

Each source defined as transient=false can be used as a field. The name of the field is the label; for example, IfInOctets.

java.lang.Double

timestamp

The timestamp of the sample.

java.util.Date

step

The step size of the response. Returns the same value for all rows.

java.lang.Long

start

The start timestamp in milliseconds of the response. Returns the same value for all rows.

java.lang.Long

end

The end timestamp in milliseconds of the response. Returns the same value for all rows.

java.lang.Long

For more details about the response, see the official Measurement API documentation.

Parameters

In addition to the queryString, the following JasperReports parameters are supported.

Parameter name Description

Parameter name	Description
Required
MEASUREMENTURL	The URL of the Measurements API; for example, `http://localhost:8980/opennms/rest/measurements`
Optional
MEASUREMENT_USERNAME	If authentication is required, specify the username; for example, "admin".
MEASUREMENT_PASSWORD	If authentication is required, specify the password; for example, "admin"

Required

MEASUREMENTURL

The URL of the Measurements API; for example, http://localhost:8980/opennms/rest/measurements

Optional

MEASUREMENT_USERNAME

If authentication is required, specify the username; for example, "admin".

MEASUREMENT_PASSWORD

If authentication is required, specify the password; for example, "admin"

Disable scheduler

When you need to disable the scheduler executing the reports, set the system property opennms.report.scheduler.enabled to false. You can set this in a .properties file in the ${OPENNMS_HOME}/etc/opennms.properties.d/ directory.

Helper methods

There are a few helper methods to help create reports in Horizon.

These helpers come with the Measurement Datasource.

Table 2. Supported helper methods for the `org.opennms.netmgt.jasper.helper.MeasurementsHelper` class
Helper method	Description
getNodeOrNodeSourceDescriptor(nodeId, foreignSource, foreignId)	Generates a node source descriptor according to the input parameters. Either `node[nodeId]` or `nodeSource[foreignSource:foreignId]` is returned. `nodeSource[foreignSource:foreignId]` is returned only if `foreignSource` and `foreignId` are not empty and not null. Otherwise, `node[nodeId]` is returned. nodeId: String, the ID of the node. foreignSource: String, the foreign source of the node. May be null. foreignId: String, the foreign ID of the node. May be null. For more details, see Node source descriptor use.
getInterfaceDescriptor(snmpifname, snmpifdescr, snmpphysaddr)	Returns the interface descriptor of a given interface; for example, `en0-005e607e9e00`. The input parameters are prioritized. If an `snmpifdescr` is specified, it is used instead of the `snmpifname`. If an `snmpphysaddr` is defined, it will be appended to `snmpifname`/`snmpifdescr`. snmpifname: String, the interface name of the interface; for example, `en0`. May be null. snmpifdescr: String, the description of the interface; for example, `en0`. May be null. snmpphysaddr: String, the MAC address of the interface; for example, `005e607e9e00`. May be null. As each input parameter may be null, not all of them can be null at the same time. At least one input parameter has to be defined. For more details, see Interface descriptor use.

Node source descriptor use

A node is addressed by a node source descriptor. The node source descriptor references the node either via the foreign source and foreign id or by the node id.

If storeByForeignSource is enabled, it is only possible to address the node via foreign source and foreign id.

To make report creation easier, there is a helper method to create the node source descriptor.

For more information, see Storing data with foreign sources on Discourse.

The following example shows the use of that helper.

jrxml report snippet to visualize the use of the node source descriptor.

<parameter name="nodeResourceDescriptor" class="java.lang.String" isForPrompting="false">
  <defaultValueExpression><![CDATA[org.opennms.netmgt.jasper.helper.MeasurementsHelper.getNodeOrNodeSourceDescriptor(String.valueOf($P{nodeid}), $Pforeignsource, $P{foreignid})]]></defaultValueExpression>
</parameter>
<queryString language="Measurement">
  <![CDATA[<query-request step="300000" start="$P{startDateTime}" end="$P{endDateTime}" maxrows="2000">
<source aggregation="AVERAGE" label="IfInOctets" attribute="ifHCInOctets" transient="false" resourceId="$P{nodeResourceDescriptor}.interfaceSnmp[en0-005e607e9e00]"/>
<source aggregation="AVERAGE" label="IfOutOctets" attribute="ifHCOutOctets" transient="false" resourceId="$P{nodeResourceDescriptor}.interfaceSnmp[en0-005e607e9e00]"/>
</query-request>]]>

Depending on the input parameters, you get either a node resource descriptor or a foreign source/foreign id resource descriptor.

Interface descriptor use

An interfaceSnmp is addressed with the exact interface descriptor. To allow easy access to the interface descriptor, we provide a helper tool. The following example shows the use of that helper.

jrxml report snippet to visualize the use of the interface descriptor

<parameter name="interface" class="java.lang.String" isForPrompting="false">
  <parameterDescription><![CDATA[]]></parameterDescription>
  <defaultValueExpression><![CDATA[org.opennms.netmgt.jasper.helper.MeasurementsHelper.getInterfaceDescriptor($P{snmpifname}, $P{snmpifdescr}, $P{snmpphysaddr})]]></defaultValueExpression>
</parameter>
<queryString language="Measurement">
  <![CDATA[<query-request step="300000" start="$P{startDateTime}" end="$P{endDateTime}" maxrows="2000">
<source aggregation="AVERAGE" label="IfInOctets" attribute="ifHCInOctets" transient="false" resourceId="node[$P{nodeid}].interfaceSnmp[$P{interface}]"/>
<source aggregation="AVERAGE" label="IfOutOctets" attribute="ifHCOutOctets" transient="false" resourceId="node[$P{nodeid}].interfaceSnmp[$P{interface}]"/>
</query-request>]]>

To get the appropriate interface descriptor depends on the input parameter.

HTTPS use

To establish a secure connection to the Measurements API, you must import the public certificate of the running Horizon to the Java Truststore. In addition, you must configure Horizon to use that Java Truststore. Please follow the instructions in this chapter to set up the Java Truststore correctly.

In addition, set the property org.opennms.netmgt.jasper.measurement.ssl.enable in $OPENNMS_HOME\etc\opennms.properties to "true" to ensure that only secure connections are established.

If org.opennms.netmgt.jasper.measurement.ssl.enable is set to "false", an accidentally insecure connection can be established to the Measurements API location. An SSL-secured connection can be established even if org.opennms.netmgt.jasper.measurement.ssl.enable is set to "false".

Limitations

Only a JDBC Datasource to the Horizon database connection can be passed to a report, or no datasource at all. One does not have to use the data source, though.

Create PDF reports from Grafana dashboards using Horizon

Horizon provides three templates to create a PDF report from an existing Grafana dashboard. You can also schedule and email these PDF reports to anyone:

Keep staff without access to Horizon informed about network performance for improved capacity planning
Create a permanent record of strategic information and progress over a long period of time

The PDF report displays each of the panels from the specified dashboard, with one, two, or four panels per page, depending on the selected template.

Dashboard to PDF:

Before you begin

This feature requires Horizon and an instance of Grafana with at least one dashboard and panel. OpenNMS lets you create a report for any Grafana dashboard, not just those created using OpenNMS Helm.

You must set up Grafana as a data source by configuring the Grafana endpoint in Horizon.

If you are using the Docker image for Grafana, you must complete additional configuration. Refer to Grafana’s Remote rendering service documentation for more information on how to set up remote rendering. Additional details are available on GitHub.

Configure the Grafana endpoint

Configuring the Grafana endpoint sets up Grafana as the data source for the dashboards from which you create PDFs.

Login to your Grafana instance.
Choose Configuration API Keys New API Key.
Specify a key name and "Viewer" role and click Add.
1. Leave the time to live blank so that the key never expires.
Copy the key so that you can paste it into the Horizon UI.
1. If desired, use the cURL command provided in the API key dialog to test the key.
In OpenNMS, click Please add a Grafana endpoint:
In the Endpoint Configuration screen click the plus sign on the right to add a new endpoint.
Fill in the information and click Test Connection.
Click Create.

You can now use Horizon to create PDF reports of Grafana dashboards.

Create a PDF of a Grafana dashboard

In the Horizon UI, choose Reports Database Reports.
In the Report Templates area, click Grafana Dashboard Report <Xppp>, where <Xppp> represents the number of panels per page you want to display.
In the Report Parameters area, specify the appropriate information.
1. Note that Grafana Endpoint is the data source. Select a Grafana dashboard from the list.
2. You can also specify CSV for report type.
Click Create Report.
1. You are prompted to save the report locally or open it. The file is saved to a folder on the Horizon server. It also appears in the UI in the Persisted Reports tab.
To send the report to someone, click Deliver this report.
Fill out the Report Delivery Options.
1. If you select Email report, specify the recipient’s email address in the Recipient field. Separate multiple recipient emails with a comma.
2. Webhook lets you post the generated report to the specified URL.
Click Deliver Report.
To schedule the report for regular delivery, click Schedule this report.
Specify the report frequency (daily, days per week, and so on) and interval of the report.
Click Schedule Report.

Scheduled reports appear in the Report Schedules tab, where you can edit or delete them: