Orion Create an SLA report showing average SLA availability during business hours

10/11/2018 11:15 AM

3/23/2022 9:10 PM

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

Notes:

• You can also create custom reports with SolarWinds SAM. See the SolarWinds SAM Getting Started Guide for an example.
• For questions and tips about SWQL, seek peer advice in the SolarWinds user forum THWACK, for example, the blog post on Intro to API, SDK & SWQL. SWQL is not supported by SolarWinds technical support.

Prerequisites 

• Nodes monitored with NPM for the period you want to report on. 
• Familiarity with custom properties.
• Familiarity with reports in the Orion Web Console.
• Familiarity with a programing language, such as SQL or SWQL. (To learn about the SWQL query featured in this KB article, see Understanding the Custom SLA Report SWQL query 

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:

 

Network Performance Monitor

Complete these tasks, as described in the following sections:

1. Create custom properties specifying business hours.
2. Specify the business hours for nodes.
3. Create an 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:

• Create a custom property
• Specify custom property values for nodes

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. 

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

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 Report 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 Report 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:

• Manage reports in the Orion Web Console
• Create reports
• Design layout for reports
• Schedule reports

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.