Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

 

 

 

Home > Success Center > Network Performance Monitor (NPM) > Create an SLA report showing average SLA availability during business hours

Create an SLA report showing average SLA availability during business hours

Overview

Create a report showing average availability of your nodes during your business hours. Use a SolarWinds Query Language (SWQL) query to customize the report.

You can also create custom reports with SolarWinds SAM. See the SolarWinds SAM Getting Started Guide for an example.

Prerequisistes

Goal

Create a daily custom service level agreement (SLA) report that shows device availability during business hours, and reports node availability as an uptime percentage.

The report will look like this:

Steps

  1. Create custom properties specifying business hours.
  2. Specify the business hours for nodes.
  3. Create a SWQL query that defines data for the report.
  4. Create a report using the query, and customize its layout.

Create custom properties for the business hours start time and end time

In this scenario, the business hours are called time_from (start time) and time_to (end time). Custom properties help specify different business hours for different nodes. These business hours are also included in the report.

  1. Log in to the Orion Web Console as an administrator.
  2. Click Settings > All Settings > Manage Custom Properties.
  3. Click Add Custom Property for Nodes, enter a name (time_from), and select Reports in the Usage area.

    Add a description that specifies the intended use of the property.

  4. Click Submit.

    You have created the custom property and are returned to the Manage Custom Properties view.

  5. Repeat the steps for time_to.

After you have created custom properties for the start and end time of your business hours, specify the business hours for your nodes.

Specify business hours for nodes

This scenario uses the Custom Property Editor to define business hour values so that you can also view monitored nodes and their custom properties.

  1. On the Manage Custom Properties view, select time_to and time_from, and click View/Edit Values.
  2. Select the nodes, and click Edit Multiple Values.
  3. Select TIME_FROM, and provide the business hours start time.
  4. Select TIME_TO, provide the business hours end time, and click Save Changes.

The start and end times are applied on the selected nodes. Develop the query, and create the report.

For more information about custom properties, see:

Develop the SWQL query in SWQL Studio

Use the SolarWinds Query Language (SWQL) to get data for the report from the SolarWinds Information Service (SWIS). SolarWinds strongly recommends against using SQL to access the Orion platform database directly. SWQL and SWIS provide the following advantages:

  • Prevents your reports from breaking if a software update changes the structure of the database
  • Helps to ensure data consistency and performance optimization

The following SWQL query retrieves the data needed for the report. If you are not interested in using SWQL Studio to further develop the SWQL query, copy the following code, and continue to Specify the report layout

SELECT n.Caption, sub_query.sla_day, sub_query.sla_availability, n.CustomProperties.time_from, n.CustomProperties.time_to FROM
(
   SELECT avg(Availability) AS sla_availability, datetrunc('day',datetime) AS sla_day, r.NodeID
   FROM Orion.ResponseTime r
   WHERE hour(datetime) >= r.Node.customproperties.time_from
   AND hour(datetime)<= r.Node.customproperties.time_to
   GROUP BY datetrunc('day',datetime), r.NodeID
) AS sub_query


INNER JOIN Orion.Nodes n
ON n.NodeID=sub_query.NodeID

To learn how this SWQL query works, see the Understanding the Custom SLA Reports SWQL query KB article. Suitable for beginners and advanced users alike, it provides a detailed explanation of the query presented in this article and shows how to develop it further.

Install SWQL Studio and run the query

  1. Download the latest OrionSDK.msi file from the Orion SDK project on GitHub. 
  2. Run the file to install SWQL Studio. 
    SWQL Studio is installed in the following location by default:
    C:\Program Files (x86)\SolarWinds\Orion SDK\SWQL Studio\SwqlStudio.exe
  3. Open SWQL Studio and configure it:
    1. Enter the FQDN of your Orion platform server (or IP Address).
    2. In Server Type, select Orion (v3). This defines the version of SWIS.
    3. Enter the Administrator credentials for Orion Web Console, and click Connect. 

      SWQL Studio opens. Note that the tree on the left side shows the structure Orion uses for viewing data, not the SQL server structure.

  4. Copy and paste the query into SWQL Studio. 
    To learn how the query works, see the Understanding the Custom SLA Reports SWQL query KB article. 
  5. Press F5 to run the query and view the query results.

    .

Specify the report layout

When you defined the data for the report, specify what you want to see in the report and how the data should be sorted.

  1. In the Orion Web Console, click Reports > All Reports, and click Manage Reports.
  2. Click Create New Report, select Custom Table, and click Select and Continue.
  3. In Selection Method, select Advanced Database Query, and Query Type SWQL.
  4. Paste the SWQL query into the box, and click Add to Layout.
  5. Click Add Column, select the columns for the report, and click Add Column.

    The default columns are specified in the query: Caption, sla_day, sla_availability, time_from, and time_to.

  6. In the Sort results by, select sla_day, and then select descending, so that you have the nodes with the highest availability listed at the top of the report.


  7. Click Submit, enter a name for the report, and click Next.
  8. Preview the report, and complete the wizard:
    1. Specify the report properties (optional).
    2. Schedule the report to run regularly (optional).
    3. Review the report summary, and click Submit.

For more details about reports, see:

What's Next

You have created a custom SWQL-based report that provides the average availability of nodes during business hours.

For more information, see Create and view reports

Last modified
14:55, 17 Feb 2017

Tags

Classifications

Public