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

SWQL Walkthrough

Level 11

Hi, all!  And welcome back to our discussion on the SDK and all things SWQL. So, at this point, we’ve briefly introduced SWQL, but today we’re going to get down to building queries with SWQL and talk about what you can make with them.

But first, let’s discuss why this is important. The Orion® Platform does an excellent job of giving you a single-pane-of-glass view for all of your products, while giving you the freedom to pick and choose which modules you wish to purchase. Because of this, the back-end database is fairly segregated, save for a handful of canned widgets and reports. For database experts, this would be done by waving a magic wand and using the correct incantations of “Inner join!” or “Outer Join!”  For the rest of this, SWQL can do the trick!

Now, what this is NOT going to be is a general guide on structured query language (SQL). This does require some level of knowledge of how to construct a basic query, but don’t be scared off just yet.  SWQL, let alone SQL, is not something I knew before I started, but I picked up quite easily.

Also, before we begin, if you haven’t picked up the latest SWQL studio, I highly recommend you do so and check out our most recent post here. This can be installed anywhere that has an Orion server connection, including your laptop.

Now that that is out of the way, let’s get to the meat of the subject. SWQL, in and of itself, is very similar to SQL, but with a few restrictions. For example, SWQL and its studio is read-only. There are SWIS calls you can make with it for API/PowerShell®, but we’ll get into that in a later post. For a quick rundown, check out this post.

If I wanted to see a list of applications that I’m monitoring on my SAM instance, I could write a query that looks like this:

select Displayname from Orion.APM.Application

And I go to Query à Execute (or F5),

pastedImage_0.png

that will get me an output that looks like this:

pastedImage_1.png

Pretty simple, right?  Let’s look and see what this does, though:

“select” and “from” should be self-explanatory if you know a little SQL. “select” means we want to retrieve/read information, and “from” is stating which table we’re getting that information from.  Orion.APM.Application is the table name (so, we’re getting information “from” “Orion.APM.Application”), and “DisplayName” is the title of the column we’re getting that information from.  Now, where did we get that column name from?  If you look at SWQL studio and find the table name and expand on it, you’ll get this:

pastedImage_2.png

Those icons with the blue icons next to them? Those are the columns we can pick from. For the other icons, check out our previous post here.

Let’s add some more to that query. If we want to see the status of the applications (up/down/warning/etc.), we can just add the status to the query, like so:

select Displayname, Status from Orion.APM.Application

This will give us:

pastedImage_3.png

More info on the status values can be found here, but, 1 is up, 2 is down, etc.

Now, what if we wanted to select ALL of the columns in this table to see what we get. Unfortunately, this is one of the first things that differs from SQL to SWQL that you cannot wildcard with *.  In other words:

select * from <tablename> does NOT work!!!

If you want all columns, you’ll have to select each one of them, separated by a comma. Luckily, SWQL will do this for you. If you right-click on the table name, you have the option of creating a general select statement:

pastedImage_4.png

That will generate a query for you with EVERY column possible for that table.

Pretty neat, right? Now, let’s get to the fun part of SWQL. One of the attributes of SWQL over SQL is its ability to automatically link related tables. If you are familiar with SQL, this is where things would normally get hairy with inner/outer join statements. Here we make it easier.

Let’s use our application example again. Having the list of applications is great, but to me, it’s nothing unless I know which node it is tied to. There is a node id in that application table, but it returns a number, which means nothing to me.  Remember those chain link icons from earlier?  Those are linked tables, and if you look, there is a link to the Orion.Nodes table:

pastedImage_5.png

To get the linkage to work, we first need to give the application table an alias. To do so, I just need to give it a name after the table declaration.  So, let’s call it “OAA,” which is short for Orion APM Application. Note: you can name it anything EXCEPT for a reserved word, like “select” or “from.”

select Displayname, Status from Orion.APM.Application OAA

Now, we need to make sure our columns are referenced to OAA by adding that to the beginning of the column names:

select OAA.Displayname, OAA.Status from Orion.APM.Application OAA

Finally we add the node name.  We can do this with the linked table from earlier by using dot notation.  In other words, if I write in “OAA.Node.”, I’m now allowed to pick any column from the node table, including the name of the node (or “caption”).  Now my query looks like this:

select OAA.Displayname, OAA.Status, OAA.Node.Caption from Orion.APM.Application OAA

And now, this is my output:

pastedImage_6.png

This is where things get interesting. Remember how I said that we can tie multiple products together?  The AppStack dashboard with Virtualization Manager and Storage Resource Monitor is extremely powerful, especially in terms of reporting. SWQL can help us with that.

If we keep going with our Application example above, we can continue tying information together from other tables.  So far, we’ve linked the nodes table, but let’s see what ESX host these belong to. From the Applications table, there isn’t a link to any of the VIM tables:

pastedImage_7.png

But it is related to the “Nodes” table, and if we look at the Nodes table:

pastedImage_8.png

Then we go to the Virtual Machines table and from Virtual Machines table…

pastedImage_9.png

… there’s the Hosts table!  So, let’s link that to our query using dot notation:

select OAA.Displayname, OAA.Status, OAA.Node.Caption, OAA.Node.VirtualMachine.Host.HostName from Orion.APM.Application OAA

pastedImage_10.png

Note, the host names that are NULL refer to machines that are not monitored via Virtualization Manager; they do not have a host.

That’s it for now. Later, we’re going to learn some more tricks for formatting with SWQL, and then how to apply this to Orion®. Stayed tuned for the next one!

15 Comments
MVP
MVP

Great post.  I expect you guys might be covering it in the later post, but just to put it out there because people inevitably ask.

You can get around all the status 1 = up stuff by joining in the Orion.StatusInfo table like so:

select OAA.Displayname, OAA.Status, s.StatusName, OAA.Node.Caption from Orion.APM.Application OAA

join orion.StatusInfo s on s.StatusId=OAA.status

pastedImage_0.png

Level 11

I will now for sure.  In the coming posts will be one on formatting, so I'll be sure to include this as well.  Thanks!!

MVP
MVP

Very nice information. I really want to get a handle on swql. I haven't had a lot of need for it, but I'm sure as I learn there will be lots of cases where I find it useful. Thanks for the post.

Level 14

When's the next post, I'm thirsty for more knowledge? I'm finally starting to understand this "swick-le" stuff.

D

Level 8

Some of the BEST explanations of accomplishing Real Work(tm) without all those nasty Joins I've seen yet!

Clear and Concise! THANKS!

This is the best explanation I've found yet for SWQL. 

It's particularly helpful when someone builds a document that doesn't assume the audience knows anything at all about SQL or databases or queries, their jargon or syntax.

Level 9

When can we expect the next post. its really interesting and useful.

Level 12

Hi

Like the above can you show how to build a table with Nodename, Status, Scheduled unmange from , scheduled unmange  to , By which account. Only for future scheduled

Level 7

This is what i was looking for, very nice information. Please post more on SWQL

Level 7

Very helpful -- However I'm running into an issue where it appears to do an outerjoin.

My Query:

SELECT N.nodeID,N.sysName,N.sysDescr,N.vendor,N.OSImage,N.OSVersion,N.Username,N.Password,N.Description,

N.EntityPhysical.HardwareRevision,N.EntityPhysical.FirmwareRevision,N.EntityPhysical.SoftwareRevision from NCM.Nodes N

I have two table here:  1) NCM.NODES and NCM.EntityPhysical which is linked to the Nodes table.

This query runs FOREVER and dupes like a mofo.

This query ONLY pulling from the NCM.Nodes table and not getting any linked tables returns 339 rows.

SELECT N.nodeID,N.sysName,N.sysDescr,N.vendor,N.OSImage,N.OSVersion,N.Username,N.Password,N.Description from NCM.Nodes N

That's not very helpful to join the tables if it does an outer join on them.  Am I doing something wrong?   I assumed (mother of all screwups) that it would only get the rows from Entity Physical that are also in Nodes.   I did verify that there are not multiple HW,SW,Firmware Revisions.  

Any ideas?

Thanks

MVP
MVP

Have you just tried to force the join the way you expect and confirm if the built in link works the same way?  I've only ever seen it do inner joins, but maybe you stumbled across a bug in how that link was built?

Level 7

I ran this

SELECT N.nodeID,N.sysName,N.sysDescr,N.vendor,N.OSImage,N.OSVersion,N.Description from NCM.Nodes N where sysName = '%s'", 'REDACTED'  -- This gets me 1 row

SELECT N.nodeID,N.sysName,N.sysDescr,N.vendor,N.OSImage,N.OSVersion,N.Description,N.EntityPhysical.AssetID,N.EntityPhysical.Model from NCM.Nodes N where sysName = '%s'", 'REDACTED' -- This gets me 5 rows

and finally

SELECT N.nodeID,N.sysName,N.sysDescr,N.vendor,N.OSImage,N.OSVersion,N.Description,EP.AssetID,EP.Model from NCM.Nodes N inner join NCM.ENTITYPhysical EP on N.nodeId = EP.nodeID where sysName = 'REDACTED' -- Alos gets me 5 rows

Dupes all the way around.  

MVP
MVP

You can't really compare "dupes" in joins without including every column, there is likely some column on the second table that does differ you just arent displaying it in your results.

The easy with to get around it is just to select distinct.

Level 7

The link to see what the Status values mean is broken

http://www.solarwinds.com/documentation/en/flarehelp/sam/content/core-status-values-sw1133.htm?CMPSo...

So far, I haven't been able to figure out where the values are defined for the status objects in any instance (ie. NPM, NCM). The schemas just say "status" without explaining what the possible values mean.

MVP
MVP