Orion Platform SWQL, THWACK! Now I got it.

This is an overly simplified guide for "Keep the lights on" SQL to "Expert" SQL users wanting to jump into SolarWinds Query Language (aka Orion API) as fast as possible.

Who can use SWQL?

• Any user that has web credentials to the web console can use those credentials to run scripts.

What is SWQL?

• Orion SWQL or SolarWinds Query Language is a concoction of MS SQL type Elements to create statements and scripts to obtain polled information. Base Example: Select Column from Table

Where can SolarWinds Query Language (SWQL) be used?

• API Scripts
• SWQL Studio, downloaded from GitHub
• Custom Query Resource on the Web Console
• Reporting to filter what is listed in the reports.
• URL: <solarwindsserver>/Orion/Admin/swis.aspx

How can SWQL be used?

• Pull/Push data into Orion Platform
• Custom query widgets
• Alerting
• Modern Dashboards

Why SWQL, why not the Orion Database Directly?

• If you update and talk to the database directly, you may have to modify the script to get working again.
• SWQL statements are designed to not change between versions.
• Some of the only ways to join data correctly is through SWQL. Certain relationships are not in the Orion DB.

Last Note: A lot of this information already exists in multiple places, I am just combining the different points to help provide a better picture to start creating queries easily.

SQL Constructs Supported

SWQL supports the following constructs from SQL:

Note: If you know MS SQL, this will be exactly the same, except for the table names all start with Orion instead of dbo; however see the that * and UPDATE and DELETE are not possible.

• 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 SELECT clause
• Subqueries in FROM/JOIN clauses

Aggregate functions:

• SUM
• MAX
• MIN
• AVG
• COUNT

Regular functions:

• ISNULL
• ABS

Wildcard character for string comparisons in SWQL syntax is the same as SQL (%).  The asterisk (*) does not work.

Examples:

• Starts with: WHERE Column Like 'Value%'
• Ends with: WHERE Column Like '%Value'
• Contains: WHERE Column Like '%Value%'

SQL NOT Supported

• SELECT * FROM Table… (You must list the columns you want to select.)
• UPDATE, INSERT, DELETE, etc. (You can only use SWQL to read data, and changes can only be done through invoking verbs)

There are many, many more functions, specifically built for SWQL that are exposed via the API.  For the latest information, you can query the API itself and request information about the metadata functions.

Included in the Excel Spreadsheet

• SWIS Examples.
  • Understand all of the different SWQL Functions. If you want to do more than just join a table, this included converting time, check values, and the more basic SQL type commands min, max, average, etc.
• SWIS Table Reference.
  • Want to know where to find the Node Data or the Applications, Storage or Virtual etc would be within SWQL? This covers the main key reference tables, the identifier id, and how to reference it up the chain from Component to Application to Node to Engine.
  • Some of this information came from Orion.NetObjectTypes. SWIS Parent and Column Details were added to see the relationship to the next table.
  • This section will be continuously updated. The main Core modules are currently mapped, and others are still being finalized.
• Filtering by Status
  • Want to know what all of the 20+ Device and other Statuses and how convert from an ID to a human readable status? This tab is just for you. This can be referenced in Orion.StatusInfo