PURPOSE: 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) used?
SWQL Studio, downloaded from Github
Custom Query Resource on the Web Console
Reporting to filter what is listed in the reports.
How can SWQL be used used?
Pull/Push data into Orion Platform
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 or Deletes are not possible.
SELECT … FROM …
GROUP BY clauses and HAVING clauses
ORDER BY clauses
LEFT OUTER JOIN
RIGHT OUTER JOIN
UNION and UNION ALL
SELECT TOP n
Subqueries in SELECT clause
Subqueries in FROM/JOIN clauses
Wildcard character in SWQL syntax is: %, * will not work.
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 invoke)
Included in the Excel Spreadsheet
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.
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
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community.
More than 150,000 members are here to solve problems, share technology and best practices, and directly
contribute to our product development process.
Learn more today by joining now.