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.

Using existing reports

OpenNMS provides the following sample report templates, located in ${OPENNMS_HOME}/etc/report-templates, which you can customize to suit your needs:

Report Name	Category	Description
Availability Summary	Status	Summary of statistics on outages and availability for a user-specified node tag during a user-specified number of days.
Availability by Node	Status	List of availability statistics for each node in a user-specified node tag during a user-specified number of days.
Default Calendar	Status	Same availability information as Default Classic except that the two tables on daily availability are presented as a calendar. This is not a JasperReport.
Default Classic	Status	Availability information of network focusing on last month and current month to date. Information on devices with most problems and monitored services. This is not a JasperReport.
Event Analysis	Status	Tables and charts showing different slices on the top 25 events over the past 30 days.
Grafana Dashboards	Grafana	Create Report from Grafana Dashboard, three options are provided.
Interface Availability	Status	Shows the availability of interfaces with outages within a time range.
Maintenance Contracts Expired	Asset Management	Shows which assets with maintenance contracts are going to expire within a given time range.
Maintenance Contracts Strategy	Asset Management	Provides an overview of assets, showing how many within each age group have an active contract, no contract, or an expired contract. It also shows how many will expire in the next 12 months.
Early Morning Report	Status	Summary of network node status over the past 24 hours.
Response Time Summary	Status	Summary of response times for nodes with a given tag.
Response Time by Node	Status	How long it takes a node to respond, listed by node.
Serial Interface Utilization Summary	Status	Summary of serial interface utilization.
SNMP Interface Availability	Status	Status of SNMP interfaces over time range.
Total Bytes Transferred by Interface	Usage	Bytes transferred by interface.
Average and Peak Traffic Rates for Nodes by Interface	Usage	Average and peak traffic rates for nodes by interface.

Report Name

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

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. Refer to the 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 section 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.