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) > SolarWinds Query Language (SWQL)

SolarWinds Query Language (SWQL)

Created by Ian Rossiter, last modified by Mike Wethington on Feb 21, 2017

Views: 21 Votes: 2 Revisions: 9

Updated August 19, 2016

Overview

What is SWQL?

SolarWinds Query Language (SWQL) is a proprietary, read-only subset of SQL. Similar to SQL, you can use SWQL to query your SolarWinds database for specific network information. The following guide provides examples of SWQL syntax and valid SWQL queries.

Environment

NPM with SQL

Details

Common SQL Constructs Supported

SWQL supports the following constructs from SQL:
Note: You must use CRUD operations to create, read, update, or delete entities.

  • SELECT … FROM …
  • WHERE clauses
  • GROUP BY clauses and HAVING clauses
  • ORDER BY clauses
  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • UNION and UNION ALL
  • SELECT TOP n
  • SELECT DISTINCT
  • Subqueries in the SELECT clause
  • Subqueries in the FROM/JOIN clauses

SWQL supports the following aggregate functions:

  • SUM
  • MAX
  • MIN
  • AVG
  • COUNT

SWQL supports the following regular functions:

  • ISNULL
  • ABS

 

Common SQL Constructs Not Supported

SWQL does not support the following common SQL constructs:
Note: You must use CRUD operations to create, read, update, or delete entities.

  • SELECT * FROM … (You must list the actual properties you want to select.)
  • UPDATE, INSERT, DELETE, etc. (You can only use SWQL to read data.)

 

Constructs Unique to SWQL

SWQL supports various features that have no direct analog in SQL.

 

Navigation Properties

In SWQL, you can connect different entities (which are like tables or views in SQL) by relationships. You can traverse these relationships by using navigation properties in your queries. The closest equivalent in SQL is a join over a foreign key relationship.

For example, Orion.Nodes is connected to Orion.NPM.Interfaces by a hosting relationship that defines an Interfaces property on Orion.Nodes and a Node property on Orion.Interfaces. To get the contact associated with the node for all wireless interfaces, you could use a query like the following:

SELECT I.Caption, I.Node.Contact
FROM Orion.NPM.Interfaces I
WHERE I.TypeName='ieee80211'

 

This is easier to read and write than a SQL query to get the same data from the Orion database:

SELECT I.Caption, N.Contact
FROM Interfaces I
INNER JOIN Nodes AS N ON I.NodeID = N.NodeID
WHERE I.InterfaceTypeName='ieee80211

You can only use one navigation property in a single expression. This prevents you from doing something like I.Node.CustomProperties.City because Node and CustomProperties are both navigation properties.

 

Row Windows

To fetch just a subset of the rows returned (for example for paging through a large result set), add a WITH ROWS start TO end  clause after the ORDER BY clause.

For example, to get the second hundred nodes, you could use this query:

SELECT NodeID, Caption, IPAddress, Status
FROM Orion.Nodes
ORDER BY Caption
WITH ROWS 100 TO 199

 

Wildcards

The wildcard character in SWQL syntax is: %.
Example: Node.Caption Like 'AX3%'

 

Filtering by Custom Property

The proper syntax to filter by custom property is:
dataType.CustomProperties.propertyName
Example filter to only show nodes with the custom property City that matches Atlanta:
Node.CustomProperties.City = 'Atlanta'

 

Filtering by Built-in Properties

Many properties have the same name between data types. To prevent ambiguity, SolarWinds SAM prefixes the property names with the data type.

 

Examples

Example filter to show data from Cisco devices:
Node.Vendor = 'Cisco'

Example filter to show data from Windows Server 2003-2008 applications:
Application.Name = 'Windows Server 2003-2008'

Example filter to show data from devices beginning with "AX3":
Node.Caption Like 'AX3%'

Example filter to show data from Process Monitor – SNMP type component monitors:
Monitor.ComponentType = 8

 

Filtering by Status

To filter by the status property, you must know the valid status levels.

Level Status
0 Unknown
1 Up
2 Down
3 Warning
14 Critical

 

Example filter to only show monitors that are not down:

MonitorStatus.Availability<>2

 

Built-in SWQL Nodes Properties

Node.AvgResponseTime Node.CPULoad Node.Caption
Node.Contact Node.DNS Node.Description
Node.GroupStatus Node.IOSImage Node.IOSVersion
Node.IPAddress Node.LastBoot Node.LastSync
Node.Location Node.MachineType Node.MaxResponseTime
Node.MemoryUsed Node.MinResponseTime Node.NodeID
Node.ObjectSubType Node.OrionID Node.PercentLoss
Node.PercentMemoryUsed Node.ResponseTime Node.Severity
Node.Status Node.StatusDescription Node.SysName
Node.SysObjectID Node.SystemUpTime Node.TotalMemory
Node.Vendor Node.VendorIcon Node.ID
Last modified
15:17, 21 Feb 2017

Tags

Classifications

Public