Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Network Performance Monitor (NPM) > Understanding the Custom SLA Report SWQL query

Understanding the Custom SLA Report SWQL query

 

Overview

This KB article explains how the SWQL query from the Custom SLA Report article works. If you are new to building SQL and SQL-like queries, this article will help you understand how queries work. 

Be sure to complete the steps in the Custom SLA Report article before you continue with this one. The other article explains the purpose of the SLA report query and shows you how to get started with SWQL Studio.
Also: Be aware that the SLA report query uses two custom properties. To create the custom properties, go to the Custom SLA Report article and follow steps one and two, otherwise the query will not work.

Before you begin

To get the most out of this article you should have a basic understanding of the following concepts:

  • The business logic and data access layer for the Orion Platform is called the SolarWinds Information Service, or SWIS (pronounced Swiss). SolarWinds strongly recommends using the SWIS API to get data from the database instead of querying the Orion database directly. One reason to use SWIS is to prevent your queries from breaking if the Orion database changes between releases. To learn more about SWIS, see About the SolarWinds Information Service.
     
  • SWIS has its own SQL-like language called SolarWinds Query Language, or SWQL (pronounced SWICK’ul). SWQL keywords and syntax are based on SQL. Like SQL, SWQL is not case-sensitive. For example, the keywords SELECT and select are equivalent, as are property names, such as DateTime and datetime. SWQL is the focus of this article.
     
  • SWIS has a query development tool that you will use to run your queries: SWQL Studio. SWQL Studio automatically highlights SWQL key words in blue to make query statements easier to read and write. To execute a query in SWQL Studio press the F5 key. See the Custom SLA Report article for help installing and running SWQL Studio.
     
  • This KB article refers to entities and properties. In SWIS, entities are similar to database tables, and properties are similar to database columns. Entities and properties are documented in the SWIS schema documentation. Custom properties are special properties that you create to store information about your monitored devices, for example the time_from and time_to properties that you created to define business hours on a per-node basis.
     
  • The SLA report query uses SWQL functions. There are currently 55 built-in SWQL functions that you can use in queries, including numeric functions, string functions, array functions, date/time functions, and more. SWQL functions are documented on the SWQL Functions wiki page in the Orion SDK. The custom SLA report query uses three functions: two date/time functions—datetrunc() and hour()—and the avg() function.

The custom SLA report SWQL query

For reference, here is the whole query from the Custom SLA Report article:

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

 

This query has two parts: a subquery and an outer query.

  • The subquery returns the daily average uptime for each system node between the hours defined by the time_from and time_to properties.
  • The outer query returns:
    • The average uptime results and dates reported by the subquery
    • The name of each system node
    • The time_from and time_to property values for each node.ID

The subquery selects its records from the Orion.ResponseTime entity, and the outer query selects its records from the Orion.Nodes entity. (More on these entities later.) The subquery is wrapped in parentheses, so it is evaluated first in the order of operations.

Paste the query into SWQL Studio and press the F5 key to run it. You should see results similar to the following:

Screen capture showing SWQL query results in SWQL Studio

If you get an error that says Property time_from not found on target of navigation property n.CustomProperties, it's because you have not yet created the two custom properties mentioned at the top of this article.

Examining the SELECT statement in the subquery

This is the SELECT statement in the subquery:

(
   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

 

We will examine the SELECT statement line-by-line starting with the FROM clause:
 

   FROM Orion.ResponseTime r
 

FROM is the SWQL keyword that specifies the entity that you want to retrieve data from. In this case, you want to retrieve data from the Orion.ResponseTime entity because that is the entity that holds statistical information about response times in Orion.

To view the Orion.ResponseTime entity’s 18 child properties, see the SWIS schema documentation.

The letter r at the end of the FROM clause is an alias declaration. A SWQL alias can be used to create a short temporary name for an entity or property. This allows you to refer to the Orion.ResponseTime entity using the temporary name r. So for example, r.NodeID is equivalent to writing Orion.ResponseTime.NodeID.

 

The SELECT command

Now go back one line and look at the SELECT command:
 

SELECT avg(Availability) AS sla_availability, datetrunc('day',DateTime) AS sla_day, r.NodeID 

The SELECT command names three properties in the Orion.ResponseTime entity: the Availability property, the DateTime property, and the NodeID property.

 

  • The Availability property captures node uptime data. Any time a node is polled, Availability is either 100 (available) or 0 (unavailable).

    The built-in SWQL avg() function calculates the average value of the Availability data points. This number is your uptime-as-a-percentage value, and you alias the results as sla_availability.

    The Availability property stores granular uptime data during the "Detail" retention period (which is seven days by default). After that, the system discards old data and replaces it with averaged values:
      • Between 7 and 30 days, each record in Orion.ResponseTime represents a rolled-up average of one hour's data.
      • Between 30 and 365 days, each record represents a rolled-up average of one day's data.

  • The DateTime property stores timestamp values that indicate when each system was polled for data. These values are in UTC.

    The datetrunc() function removes unnecessary details from the timestamp records. The function takes two parameters: the first specifies the data segment to truncate (so specifying day as the attribute value tells the function to keep day, month, and year details, but remove hour and minute details). The second parameter specifies the SWIS property that contains the records that the function should process. You alias the results of the datetrunc() function as sla_day.

  • The NodeID property stores numeric values that uniquely identify each system on the network. Because the alias r refers to the Orion.ResponseTime entity, r.NodeID signifies the values stored in the NodeID property in the Orion.ResponseTime entity.

The WHERE clause

After the FROM clause is a WHERE clause. The WHERE clause restricts the query to records that meet the specified criteria:
 

WHERE hour(DateTime) >= r.Node.customproperties.time_from
AND hour(DateTime)<= r.Node.customproperties.time_to

 

In this case, the WHERE clause specifies timestamps that fall within business hours.

A valid timestamp is one that is:

  • greater than or equal to the time_from value specified for each monitored node, and
  • less than or equal to the time_to value specified

Note that the time_from and time_to properties are custom properties that you created using the Orion Web Console in the previous KB article. Use the "." operator to specify custom properties. Simply type [alias].Node.CustomProperties.[custom property name] to use the property in your query. (You do not need to know where the information is stored in the database.)

The WHERE clause uses the hour() function to extract just the hour from the timestamp information stored in the DateTime property. Then the query evaluates if the hour falls into the range specified by the time_from and time_to properties.

The GROUP BY clause

The GROUP BY clause follows the WHERE clause:
 

    GROUP BY datetrunc('day',DateTime), r.NodeID
 

Because your SELECT statement calculates up-time data across multiple records, the statement needs a GROUP BY clause to collect the averaged data and group the results, first by day, and then by node ID.  Again, the datetrunc() function drops hour and minute details, but keeps the day, month, and year.

Finally, wrap your subquery in parentheses and use the AS keyword to assign an alias to the subquery. The script shown here uses sub_query as the alias name. Later you will use this alias to get the results of the subquery. 

  (

     …

 ) AS sub_query

The INNER JOIN

Following the subquery, you have an INNER JOIN.
 

INNER JOIN Orion.Nodes n

ON n.NodeID=sub_query.NodeID
 

An INNER JOIN joins multiple entities and returns all rows where the JOIN condition is met. In this case, you JOIN the Orion.Nodes entity and the results of the subquery. The JOIN is on the NodeID property because that is the property that both entities share in common. The JOIN condition returns rows from both entities if the Orion.Nodes.NodeID column and the sub_query.NodeID column share matching values. (You will see the reason for the JOIN in the next section, “Examining the Outer Query.”) Finally, note that the Orion.Nodes entity is assigned to the alias n. In the next section you will see that the outer query also references the n alias.

Examining the outer query

You are done examining the inner query. Now take a look at the outer query:
 

SELECT n.Caption, sub_query.sla_day, sub_query.sla_availability, n.CustomProperties.time_from, n.CustomProperties.time_to FROM
 

The outer query outputs select data from both the sub_query and the Orion.Nodes entity. The need to retrieve records from two separate entities is the reason why you JOINed the subquery and the Orion.Nodes entity.  

  • To display the names of the NodeIDs in your report, you need to retrieve the data from the Caption property in the Orion.Nodes entity. (The NodeID property in the Orion.ResponseTime entity only contains numerical IDs.) Because the Orion.Nodes entity is assigned the alias n in the INNER JOIN, write n.Caption to specify the Caption property.
  • The second and third columns of data (sub_query.sla_day, sub_query.sla_availability) are pulled from the sub-query. 
  • The time_from and time_to values are retrieved from the Orion.Nodes entity, same as the Caption values: n.CustomProperties.time_from, n.CustomProperties.time_to

Note that this SELECT statement does not require a GROUP BY clause because it is not averaging multiple records. Also, the FROM keyword that follows the SELECT command specifies that the outer query retrieves data from the subquery.

 

Press the F5 key to execute the query and see the results.

Adding detail

The information presented so far covers the basic query in the Custom SLA Report article. Now that you know how the query works, you can easily add additional information. For example, in SWQL Studio notice that the Orion.Nodes entity includes a Description property. To add the Description property as a column in your query, add n.Description to the outer query SELECT statement like this:
 

SELECT n.Caption, sub_query.sla_day, sub_query.sla_availability, n.CustomProperties.time_from, n.CustomProperties.time_to, n.Description

Press the F5 key. The revised report with the added Description column should look similar to the following:

Screen capture showing updated SWQL query results in SWQL Studio

 

 

For more information

 

 

 

Last modified
16:00, 28 Nov 2016

Tags

Classifications

Public