Database Reports

Reporting on data from your monitoring system is important for strategic and operational decisions. Database reports provide access to the Horizon JasperReports engine, and let you create and customize report templates. You can run reports on demand or on a predefined schedule.

Originally, database reports created reports based only on data stored in the Horizon database. This is no longer mandatory; you can also use performance data. Theoretically, 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 those features; it describes only reports using the JasperReports engine and Grafana dashboards.

Overview

The Horizon Report Engine uses the JasperReport library to create reports in various formats. Each report template must be saved as a *.jrxml file.

Every time a report runs, the Report Engine passes it a JDBC connection to the Horizon database.

Only a JDBC connection to the Horizon database can be passed to the report.

Report Engine features
Supported Output Formats	PDF, CSV
JasperReport Version	6.3.0

For more details on how JasperReports works, see the official Jaspersoft Studio documentation.

Report templates

Horizon includes the following sample report templates:

Report Name	Category	Description
Availability Summary	Status	Summary of outages and availability for a specified node tag, spanning a specified number of days.
Availability by Node	Status	List of availability statistics for each node in a specified node tag, spanning a specified number of days.
Default Calendar	Status	Network availability information across the previous month and the current month, to date, presented in a calendar format. Summary of devices with the most problems, and monitored services. This is not a JasperReport.
Default Classic	Status	Network availability information across the previous month and the current month, to date. Summary of devices with the 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	Three variations of reports created from a specified Grafana dashboard.
Interface Availability	Status	Availability summary of interfaces which have experienced outages, spanning a specified time range.
Maintenance Contracts Expired	Asset Management	Summary of assets whose maintenance contracts are set to expire within a specified time range.
Maintenance Contracts Strategy	Asset Management	Summary of assets, sorted by device age, and then by contract status. It also shows how many contracts are set to expire within the next 12 months.
Early Morning Report	Status	Summary of node status over the past 24 hours.
Response Time Summary	Status	Summary of response times for nodes with a specified tag.
Response Time by Node	Status	Summary of response times, listed by node.
Serial Interface Utilization Summary	Status	Summary of serial interface utilization.
SNMP Interface Availability	Status	Summary of SNMP interface status over a specified time range.
Total Bytes Transferred by Interface	Usage	Summary of bytes transferred, listed by interface.
Average and Peak Traffic Rates for Nodes by Interface	Usage	Average and peak traffic rates for nodes, listed by interface.

Report Name

Modify report templates

All default report templates are stored in ${OPENNMS_HOME}/etc/report-templates, and can be modified to suit your needs. Any changes are applied the next time Horizon creates a report.

When a subreport is modified, Horizon detects the change based on the report’s lastModified parameter and recompiles it. 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 you are not sure that your compiled subreports match the current templates, simply delete all .jasper files. Horizon automatically recompiles the subreports as needed.

Add a custom report

To add a new report to your local Horizon repository, follow these steps:

Create a new entry in ${OPENNMS_HOME}/etc/database-reports.xml:

<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. This name is displayed in the web UI.
3	Defines if this report can run 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. This description is displayed in the web UI.

Create a new entry in ${OPENNMS_HOME}/etc/jasper-reports.xml:

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

1	The identifier defined in the previous step. It 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 (either `jdbc` or `null`).

Jaspersoft Studio

We recommend that you use Jaspersoft Studio to develop new reports. You can download it via the official Jaspersoft website.

We recommend that you use the same version of Jaspersoft Studio that the Horizon JasperReport library uses. Currently, Horizon uses version 6.3.0.

Connect to Horizon database

You must create a database data adapter to run SQL queries against your Horizon database (see the official Jaspersoft Studio documentation).

Use Measurements Datasource and helpers

You must add the Measurements Datasource library to Jaspersoft Studio’s build path to use the Measurements API:

Open the Project Explorer.
Right-click in the window and select Configure Buildpath.
Navigate to the Libraries tab:
Click Add External JARs.
Select ${OPENNMS_HOME}/bin/opennms-jasperstudio-extension-{opennms-version}-jar-with-dependencies.jar.
Close the file selection dialog. The Measurements Datasource and helpers should now be available:
Open Jaspersoft Studio and navigate to the Dataset and Query dialog.
Select the Measurement language from the Language list:

If the Read Fields functionality is not available, use the Data Preview. To access the Measurements API, use the MEASUREMENT_URL, MEASUREMENT_USERNAME, and MEASUREMENT_PASSWORD parameters (see Datasource fields).

Access performance data

The Horizon Measurement Datasource lets you query the Measurements API and process the returned performance data in your reports. See Measurements API for information on how to use the API.

When you use the Measurements Datasource for a report, an HTTP connection to the Measurements API is established only if the report is not running within Horizon (for example, when run in Jaspersoft Studio).

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

Sample queryString to receive data from 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 this case, `measurement`. JasperReports supports many languages out of the box, including SQL, xpath, and so on.

Datasource fields

Each datasource returns a number of fields which you can use in reports. 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	Sample’s timestamp	java.util.Date
step	Response step size. Returns the same value for all rows.	java.lang.Long
start	Timestamp for the beginning of the response, in milliseconds. Returns the same value for all rows.	java.lang.Long
end	Timestamp for the end of the response, in milliseconds. 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

Sample’s timestamp

java.util.Date

step

Response step size. Returns the same value for all rows.

java.lang.Long

start

Timestamp for the beginning of the response, in milliseconds. Returns the same value for all rows.

java.lang.Long

end

Timestamp for the end of the response, in milliseconds. Returns the same value for all rows.

java.lang.Long

For more information, see Measurements API.

Parameters

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

Parameter Description

Parameter	Description
Required
MEASUREMENTURL	The URL of the Measurements API (for example, `http://localhost:8980/opennms/rest/measurements`).
Optional
MEASUREMENT_USERNAME	Username to access the datasource, if required (for example, "admin").
MEASUREMENT_PASSWORD	Password to access the datasource, if required (for example, "admin").

Required

MEASUREMENTURL

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

Optional

MEASUREMENT_USERNAME

Username to access the datasource, if required (for example, "admin").

MEASUREMENT_PASSWORD

Password to access the datasource, if required (for example, "admin").

Disable report scheduler

To disable the report scheduler, set opennms.report.scheduler.enabled to false. You can set it in a .properties file in ${OPENNMS_HOME}/etc/opennms.properties.d/.

Helper methods

You can use helper methods to create reports in Horizon. These helpers are included with the Measurement Datasource:

Helper Method Description

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. For more details, see Node source descriptors.
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` or `snmpifdescr`. Because each input parameter may be `null`, at least one must be defined. For more details, see Interface descriptors.

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.
For more details, see Node source descriptors.

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 or snmpifdescr.
Because each input parameter may be null, at least one must be defined.
For more details, see Interface descriptors.

Node source descriptors

A node is addressed by a node source descriptor. The node source descriptor references a node using either its foreign source and foreign ID, or its node ID. If storeByForeignSource is enabled, you can address the node using only its foreign source and foreign ID (see Storing data with foreign sources on Discourse).

To make report creation easier, the Measurement Datasource includes a helper method that creates 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, the method returns either a node resource descriptor or a foreign source/foreign ID resource descriptor.

Interface descriptors

An interface is addressed using its exact interface descriptor. The Measurement Datasource includes a helper method that allows easy access to 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>]]>

Connect to Measurements API

To establish a secure connection to the Measurements API, you must import your Horizon instance’s public certificate to the Java truststore and configure Horizon to use it (see Configure Horizon to use the Java truststore). You must also set org.opennms.netmgt.jasper.measurement.ssl.enable to true in ${OPENNMS_HOME}/etc/opennms.properties to ensure that only secure connections are established.

If org.opennms.netmgt.jasper.measurement.ssl.enable is set to false, an insecure connection to the Measurements API can be established.