The SolarWinds Query Language (SWQL) is a data retrieval mechanism for querying a SolarWinds Platform product and retrieving data. Built to mimic the Structured Query Language (SQL) used in most database management systems, it is an easy language to understand if you already possess a working grasp of SQL.
SWQL interfaces directly with the SolarWinds Information Service (SWIS) running on SolarWinds Platform products as opposed to making direct queries to the underlying database.
Structurally speaking the SolarWinds Information Service acts as an abstraction and interpretation layer between the requestor and the underlying databases. Existing outside of direct database requests, this interaction has distinctions between itself and traditional database queries.
This document cannot be a complete reference for the entire SolarWinds Query Language and questions should be asked in the SolarWinds SDK Forum.
Key differentiators between SQL and SWQL
Although the data available via SQL and SWQL is identical, there are several reasons to use SWQL as opposed to SQL. The most notable have to do with permissions and database schema upgrade protection.
Account Limitations & Credential Management
It’s a best practice to limit the number of accounts which have access to any database – even for read-only access. The best practice to maintain a zero-trust model with regards to your monitoring data would be to have only a single account with a complex password which can access your databases directly.
Since the SolarWinds Query Language operates at a higher level of abstraction, it also inherits the account permissions and limitations native to the SolarWinds Platform. Accounts which are not permitted to see certain types of data (Network Configuration Backups, Application Statistics, Community Strings, etc.) will honor those restrictions. A simplified example would be an account for network engineers is only authorized to see network devices within your infrastructure. Any queries written in SWQL and executed as a network engineer will not be able to see systems, virtualization, storage, etc. information. However, if a user had access directly to the database, they would have unfettered access to all monitoring data. This is inherently dangerous and goes against zero-trust security standards.
Using the accounts that you’ve already defined in your SolarWinds Platform to be used to query for monitoring data allows you to keep the number of accounts with direct database access to minimum while segregating the data to which those Orion accounts have access.
Insulation from Database Schema Changes
As the products on the SolarWinds Platform mature, there may be needs to update or remove tables, views, stored procedures, or other database-specific elements. The SolarWinds Query Language is not affected by these lower-level changes as it speaks with the data at a higher abstraction level. If you were to write a Web-based Report that makes direct calls to the database there is a chance that after an upgrade, the underlying database schema has been changed and the report will fail to execute. A Web-based Report that retrieves the same data, but uses SWQL, would be insulated from these changes and would continue to operate.
This is not to say that operations within the SWQL entities are without change, but those are documented within the API and are noted as being deprecated far in advance of being removed.
Syntax
Although the SWQL language syntax is similar to that of SQL, there are notable differences – specifically with the number and type of functions allowed within each language.
Similarities include:
- Standard query syntax is identical
SELECT TOP XX (PERCENT)is valid- The percent sign (
%) is used for multi-character string matching - The underscore (
_) is used for single-character string matching - Single quotes (
') are used to denote characters or strings JOINsare supported, but Navigation Properties alleviates some of the need- Sub queries are supported, and the syntax is identical
WHEREclauses follow the same syntax and Boolean logic- Aggregate functions behave the same between SWQL and SQL
GROUP BYandORDER BYoperations behave the sameHAVINGis used for filtering on returned values from aggregate functionsCASEstatements are supported, and the syntax is the same
Differences include:
- SWQL supports only
SELECTqueries.UPDATE,MERGE,DELETE, and any other action that is not read-only is not supported. This read/write functionality is supported by using Verbs (see SWQL Verbs [link to another chapter]) and specific REST calls (see Updating SWIS Data [link to another chapter]). - The asterisk (
*) for returning all fields is not supported in SWQL (SELECT * FROM…) - SWQL does not support cursors or looping within a statement.
- Common Table Expressions (CTEs) are not supported
- Temporary Tables are not supported
CASTandCONVERToperations are not supported- SWQL has several functions, specialized for working with monitoring data, outside those for SQL.
Under the surface, it’s plain that SWQL queries at some point must be converted to SQL calls. This is functionally correct and can be useful when troubleshooting. This will be covered in Troubleshooting SWQL Queries [link to another chapter].
Semantic Differences
When making a database call via SQL you are querying a table. Although the syntax is identical with SWQL, the terminology is changed. Being an API, data structured in tabular form are called “entities.” You may see either term (table or entity) in this wiki but be aware that entities are the way in which data is grouped and organized to match up with your monitoring infrastructure.
Testing SWQL Queries
Later in this chapter, we’ll be discussing how to use SWQL Studio to build an run queries, but in the meantime if you want to test queries you can use your SolarWinds Platform web interface. Accounts with admin access can navigate to http(s)://serverNameHere/Orion/admin/swis.aspx and execute SWQL queries for testing purposes.
For the purposes of the rest of this section, we’ll concentrate on the fundamental construction of SWQL queries. All queries listed in this chapter will be published in a public GitHub repository where you can download and tailor them to your environment.