Get a crash course on Network Monitoring delivered right to your inboxThis 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.

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:

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.