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 always using 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 cover 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 a language called measurement. If the Read Fields functionality is not available, use the Data preview. Access to the Measurements API is possible using the connection parameters MEASUREMENT_URL, MEASUREMENT_USERNAME and MEASUREMENT_PASSWORD. The Supported Fields section gives more details. Accessing Performance Data Before OpenNMS Horizon 17 and OpenNMS Meridian 2016, it was possible to access the performance data stored in .rrd or .jrobin files directly by using the jrobin language extension provided by the RrdDataSource. 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 only established if the report is NOT running within Horizon, e.g., when used with Jaspersoft Studio. To receive data from the Measurements API simply 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{\nodeidl}].interfaceSnmp[$P{interface}]"/> <source aggregation="AVERAGE" label="IfOutOctets" attribute="ifHCOutOctets" transient="false" resourceId="node[$P{\nodeidl}].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, etc. Fields Each datasource should return a number of fields, which can be used in the report. The Measurement Datasource supports the following fields: Field name Field type Field description <label> java.lang.Double Each Source defined as transient=false can be used as a field. The name of the field is the label, e.g., IfInOctets timestamp java.util.Date The timestamp of the sample. step java.lang.Long The Step size of the Response. Returns the same value for all rows. start java.lang.Long The Start timestamp in milliseconds of the Resopnse. Returns the same value for all rows. end java.lang.Long The End timestamp in milliseconds of the Response. Returns the same value for all rows. For more details about the Response, please refer to the official Measurement API documentation. Parameters In addition to the queryString, the following JasperReports parameters are supported. Parameter name Required Description MEASUREMENTURL yes The URL of the Measurements API, e.g., http://localhost:8980/opennms/rest/measurements MEASUREMENT_USERNAME no If authentication is required, specify the username, e.g., admin MEASUREMENT_PASSWORD no If authentication is required, specify the password, e.g., admin Disable Scheduler In cases where the scheduler executing the reports must be disabled, set the system property opennms.report.scheduler.enabled to false. You can set this in ${OPENNMS_HOME}/etc/opennms.properties or ${OPENNMS_HOME}/etc/opennms.properties.d/<my-properties-file>.properties. 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 Helper class Helper Method Description org.opennms.netmgt.jasper.helper.MeasurementsHelper getNodeOrNodeSourceDescriptor(nodeId, foreignSource, foreignId) Generates a node source descriptor according to the input paramters. Either node[nodeId] or nodeSource[foreignSource:foreignId] is returned. nodeSource[foreignSource:foreignId] is only returned if foreignSource and foreignId are not empty and not null. Otherwise node[nodeId] is always 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. org.opennms.netmgt.jasper.helper.MeasurementsHelper getInterfaceDescriptor(snmpifname, snmpifdescr, snmphysaddr) Returns the interface descriptor of a given interface, e.g., en0-005e607e9e00. The input paramaters are prioritized. If a snmpifdescr is specified, it is used instead of the snmpifname. It a snmpifdescr is defined, it will be appended to snmpifname/snmpifdescr. snmpifname: String, the interface name of the interface, e.g., en0. May be null. snmpifdescr: String, the description of the interface, e.g., en0. May be null. snmphyaddr: String, the MAC address of the interface, e.g., 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 store by foreign source is enabled addressing the node only via foreign source and foreign id is possible. To make report creation easier, there is a helper method to create the node source descriptor. For more information about store by foreign source, please see our Wiki. 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{\nodeidl}), $P\{foreignsource\}, $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 either get 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{\nodeidl}].interfaceSnmp[$P{interface}]"/> <source aggregation="AVERAGE" label="IfOutOctets" attribute="ifHCOutOctets" transient="false" resourceId="node[$P{\nodeidl}].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, Horizon must be configured to use that Java Truststore. Please follow the instructions in this chapter to set up the Java Truststore correctly. In addition please also 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 datasource, though. Creating 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 datasource by configuring the Grafana endpoint in Horizon. Configure the Grafana Endpoint Configuring the Grafana endpoint sets up Grafana as the datasource for the dashboards from which you create PDFs. Login to your Grafana instance. Choose Configuration > API Keys and click New API Key. Specify a key name and "Viewer" role and click Add. 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. If desired, test the key using the cURL command provided oi the API key dialog. 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. Creating 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. Note that Grafana Endpoint is the datasource. Select a Grafana dashboard from the list. You can also specify CSV for report type. Click Create Report. 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. If you select Email report, specify the recipient’s email address in the Recipient field. Separate multiple recipient emails with a comma. 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, etc.) and interval of the report. Click Schedule Report. Scheduled reports appear in the Report Schedules tab, where you can edit or delete them: Asset Toplogy Provider Enhanced Linkd