SWQL Walkthrough

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 OrionRegistered 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/PowerShellRegistered, 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 AppStackTm 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 OrionRegistered. Stayed tuned for the next one!
Thwack - Symbolize TM, R, and C