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

Use SolarWinds Query Language (SWQL - SWIS)

Overview

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

All versions of the Orion Platform

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 chain multiple navigation properties together in a single expression, for example: I.Node.CustomProperties.City

In this example, Node and CustomProperties are both navigation properties. (A long time ago you could only use one navigation property per SWQL expression but this limitation has since been fixed.)

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

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
17:16, 29 Jul 2017

Tags

Classifications

Public