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 user-specified surveillance category during a user-specified number of days. Availability by Node Status List of availability statistics for each node in user-specified surveillance category 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 across surveillance category. 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. 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. Please 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 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 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. One does not have to use the data source, though. IS-IS Discovery Report Templates