cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Orion Platform SWQL, Thwack! Now I got it.

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?

  • 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 used?

  • Pull/Push data into Orion Platform
  • Alerts

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 …
      • 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 in SWQL syntax is: %, * will 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 invoke)

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.
  • 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
Attachments
Comments

Wow.  It's so apparent I'm far out of the Orion-tweaking crowd by not having SQL skills.  Where does one go to find the baby steps instructions to begin the catch-up journey?

rschroeder‌‌, you're already here... Being a fellow non-SQL'er, I searched, asked, read, asked, searched, etc... right here on Thwack... and Thwack Nation delivered...

I did have numerous real world requests to fulfill, so I was on a fairly direct path in my journey for SQL knowledge... However, I found one question/quest, lead to another, and another, and so on. I am now trapped in an inescapable, and never ending SQL knowledge loop...

It would be nice, though, for a single point of reference, similar to this post, but pointing to SQL. But, like I said, I was able to get the ball rolling for myself here on Thwack.

I actually find myself going through my SQL reports/resources, and converting them over to SWQL, mainly to prepare for future changes, as well as keeping everything on the same level.

Thank you, wluther‌, for the support.  I need the pre-walk / pre-crawl instructions, so I don't damage the SQL infrastructure.  Specifically, I just saw PowerShell for the first time yesterday.  And it turns out the folks who gave me advice to use it weren't even in the right page for my needs.

But I didn't know that.  Heck, they're SQL DBA's and Oracle admins with years of training & certs & experience.  Not teachers of novices.

So I saw this morning the Orion Database Manager for my first time.  And I have some old (2007) Thwack content about a simply script that could be applied to truncate the domain from the FQDN node name in NPM.  But the old instructions reference parts of NPM that have changed, and I'm loath to fire off a SQL script without understanding the process and consequences.  Shoot, I can't even say if it's possible to recover NPM or the database if the script I apply has negative consequences.

Hence the call out to Thwack for assistance and ideas.

One link I found talks about replacing the domain name:  Is there a way to mass edit OUT FQDN?   It's not quite what I need.

Another link from 2009 How Do I Trim Node Names? went unanswered . . .

A similar-sounding query, and solution, are at Re: Node name determination, but I'm leery of applying when I don't know how to apply, where to apply, and how to recover if something breaks.

This one, too, sounded promising:  Re: Removing domain name from ${SysName}

The one that looked best is here:  is a an easy way remove domain name from the node caption  Unfortunately it doesn't reference database names or categories that match exactly what's running in my latest version.

The end goal is to better fit my nodes' names into NPM's displays, and there's no need for the FQDN when the short name will do.

Any advice for me?

rschroeder‌ If you have access to a vm, then I would recommend you setup a VM, install the modules you currently use, backup current DB, and then import db into new VM environment. (or just export the nodes table, and put it into the VM db.

Then, you can play around without worrying too much about breaking the important stuff... That is what I do. Practice, build, destroy, restart...

That does sound safest.  Thanks.

rschroeder‌ How's it going? Are you making any progress? Working on any SQL/SWQL projects yet?

Too swamped due to under-staffing.  Know any CCIE's or top-notch CCNP's with a load or design experience?   I'm looking for bodies, including a Networl Architect.  ASAP.

rschroeder‌ Sorry, can't help you there...

sean.martinez‌,

Is there a way to use wild cards with IP Address fields, particularly in the NetFlow tables?  I am working on some reports and alerts using flow traffic but I really need a way to select and filter by IP Address.  I keep getting a 'Not a valid IP Address' when I try to use LIKE with an IP Address field.  Thanks

I don't think you can use wildcards, but I think you can you range queries like > or <

thanks!  will try that out.

That will work!  Thanks alot tdanner

SWQl, a nice, very powerful tool to build new, embedded views in Solarwinds. We have used a number of SWQL queries to build a custom alarmbrowser, and an overview of nodes with snmp polling problems.   Only the debugging, I have been searching for the SWQL logfile with more information than the standard message: "Error: A query to the SolarWinds Information Service failed." but I didn't locate it until now....

Also think it would be a good idea to have the possibility to update the custom property fields in the Node table with SWQL.

I do this to the caption field nightly via SQL Server Agent. You can modify this to do exactly what you want.

UPDATE [SolarWindsOrionNA].[dbo].[Nodes]

      SET  Caption = SUBSTRING(DNS, 1, (CHARINDEX('.', DNS, 0)-1))

      WHERE (DNS is not null OR DNS != '')

      AND DNS like '%.%'

Anyone interested in getting started with SWQL should take a look at this knowledge base article​. The article details how the SWQL query at the heart of the "Custom SLA Report" KB article works. The article is written with beginners in mind, but it contains details that a more experienced SWQL coder may also find useful.

Any chance we could get an updated SWQL Examples.xlsx ?

Thanks!

I get what's going on in that set of commands.  It would be nice if there were a radio button that enabled this feature in NPM, right out of the box.

Or, it'd also be OK if one day SQL or SWQL commands looked like intuitive English phrases.  But that kind of logic would be mighty twisted on the back end to be successful.

Still, as my wife says (who's a 35-year IT Professional programmer and Manager) "Commands and interfaces should be intuitive.  If a person has to look up what something means, or must refer to the manual to get a task done, then the interface isn't complete.   It must be easily understood and recognized by someone without advanced training--particularly if the goal is simple and straightforward enough that it can be explained in a brief sentence of two-syllable words."   Or something to that effect.

 

I'm taking a look at the unmanage node sample on the GitHub page:

import requests
from orionsdk import SwisClient
from datetime import datetime, timedelta


def main():
    hostname = ''
    username = ''
    password = ''

    swis = SwisClient(hostname, username, password)
    results = swis.query('SELECT TOP 1 NodeID FROM Orion.Nodes')
    interfaceId = results['results'][0]['NodeID']
    netObjectId = 'N:{}'.format(interfaceId)
    now = datetime.utcnow()
    tomorrow = now + timedelta(days=1)
    swis.invoke('Orion.Nodes', 'Unmanage', netObjectId, now, tomorrow, False)


requests.packages.urllib3.disable_warnings()


if __name__ == '__main__':
    main()

The line below seems to grab the top node in the list and unmanage that node. While that works great, I'd like to run this script locally on my nodes and have it programmatically unmanage the current node. How can I have it dynamically grab the current Node's info and unmanage it?

results = swis.query('SELECT TOP 1 NodeID FROM Orion.Nodes')

What do you mean by "the current node"?

The node the script is running on.

You could try something like this (not tested):

import socket

results = swis.query('SELECT TOP 1 NodeID FROM Orion.Nodes WHERE DNS=@dns', dns=socket.getfqdn())

Awesome, thanks! Looks like that's working.

Hello,

I try to join the table Orion.Nodes with the table Orion.CPULoad which has more than one occurences of a node

and I want only the TOP 1 record for each one based on the timestamp field.

As I can see it can be solved by using the CROSS APPLY command, which is not supported from SWQL.

Can anyone,please, suggest me another way?

You can use a query like this:

SELECT TOP 100

    n.NodeID,

    n.IPAddress,

    n.Caption,

    l.ObservationTimestamp,

    l.AvgLoad

FROM Orion.Nodes n

INNER JOIN (

    -- Get the latest observation timestamp for each node.

    SELECT

        NodeID,

        MAX(ObservationTimestamp) AS MaxObservationTimestamp

    FROM Orion.CPULoad

    GROUP BY NodeID

) lm ON lm.NodeID = n.NodeID

-- Join to Orion.CPULoad again to get the associated details.

INNER JOIN Orion.CPULoad l ON l.NodeID = lm.NodeID AND l.ObservationTimestamp = lm.MaxObservationTimestamp

ORDER BY n.NodeID

It works as expected. Thank you very much.

Version history
Revision #:
1 of 1
Last update:
‎09-25-2015 03:34 PM
Updated by: