Product Blog

3 Posts authored by: animelov Employee
animelov

Applying SWQL Part 2

Posted by animelov Employee Nov 13, 2018

Hi, all! Welcome back to the continuation of our Primer posts on SWQL and the Orion® SDK. In the last post, we showed how to create dashboards using SWQL queries. Now we’re going to take it one step further with some other uses for SWQL:

 

Dashboards:

As with the reports, you can also add a custom SolarWinds Query Language (SWQL) query to a dashboard. If you aren’t familiar with customizing dashboards and widgets, check out these videos first:
Creating a New View
Adding and Customizing Resources

 

To get started, make sure you’re logged in as an admin to SolarWinds, or a user that has rights to make updates/changes to views. Once that’s confirmed, go to the page you wish to update, and go to the left-hand drawer and select “Customize Page.”

Search for “Custom Table” and drag and drop the widget onto your dashboard. There’s also “Custom Query,” and we’ll explore the advantages further down:

Select “Done Adding,” then “Done Editing” when complete. With your newly created widget, go ahead and select either “Edit” in the upper-right, or “Configure this resource” in the middle:

This should look familiar to the report writer’s interface at this point. Give this table a title, then click “Select Datasource.”

Change the Selection Method to “Advanced Database Query (SQL, SWQL)” and make sure the radio button is set to “SWQL.” Then copy/paste your query and preview results to make sure everything looks okay:

Select “Update Datasource” when complete. Just like the report writer above, you can select and format your columns. Once you’re finished, click submit, and you now have a custom table on your dashboard!

 

Note: The Host Name column being blank isn’t an error, these machines are not associated to a host.  We’ll explore formatting in a later post to show these as “N/A” instead.

 

Now, let’s try the custom query instead. With the “Custom Query” widget, you don’t have as many options in formatting, but it gives you two distinct advantages: the ability to paginate, and the ability to add searches. Pagination will be very important for larger lists, not only for cleanliness, but also for load times on the page you’re viewing, by restricting to X number of results at a time.

 

Again, go to the left-hand drawer and select “Customize Page,” then “Add Widget.” This time, search for “Custom Query” and drag/drop this widget to your dashboard:

Now, select “Edit” in the upper-right corner of the widget:

Notice here, you only get a box where “Select Datasource” would normally be. Go ahead and copy/paste your query in here, but since you don’t get the option of selecting the order of the columns, make sure your columns in the select statement are in the order you want them in. For example, with our query:
select OAA.Displayname, OAA.Status, OAA.Node.Caption, OAA.Node.VirtualMachine.Host.HostName from Orion.APM.Application OAA

Displayname” will be the first column, “Status” will be the second column, and so forth. So now that we have this:

That will result in a widget that looks like this:

Notice the “Page 1 of 2” at the bottom? This will help reduce clutter on your dashboards by keeping the list neat and tidy, and at the same time help with page loads, since we’re restricting to only five results. Another cool feature is the “Search” function. Edit the widget again, and this time check the “Enable Search” box:

Now you have another box to insert your query, and a note about adding a where clause for the search string. When we’re finished, we’ll have a search box on the widget page, and whatever you put in that box will go into the ${SEARCH_STRING} variable. This will change our query to add the where clause. In this case, we’re going to search on the Application name, which is our first column:

 

select OAA.Displayname, OAA.Status, OAA.Node.Caption, OAA.Node.VirtualMachine.Host.HostName from Orion.APM.Application OAA WHERE OAA.DisplayName like ‘%${SEARCH_STRING}%’

 

The keen-eyed individuals will notice we added just a little bit more here. In SWQL, if you want to do a wildcard match instead of an exact match, you use the word “like” instead of “=”. Then, you use the percent character (%) to denote a wildcard, not an asterisk (*). Finally, in SWQL you always use single quotes for strings, never double quotes. Let’s put that in our search box:

And now we have a search box!

To test, let’s search on IIS and see what we get:

There we go! Remember, this is just an application example, you can use this for anything else that you’re collecting in the product. For more examples, check out my other post for searching on a Port Description in User Device Tracker (UDT): https://thwack.solarwinds.com/docs/DOC-192885

 

 

That’s it for now! Stay tuned for future posts on formatting SWQL queries in these reports!

Hi, all! Welcome back to the continuation of our Primer posts on SWQL and the Orion® SDK. In the last post, we established how to create a query, and how to link tables together to get useful information. In this post, we’re going to discuss how to apply these to your Orion Platform products.

If you don’t feel entirely comfortable creating a query, I highly encourage you to read over the last post. It serves as a foundation for today’s discussion.

 

Reports:

The first and most obvious application for SWQL queries is generating Orion reports in the web-based report writer. To start, go to Reports à All Reports, then select “Manage Reports” in the upper right corner:

 

 

From here, click on “Create New Report”:

 

 

If this is your first time creating any report, you will see a nice splash page on how to create reports. I highly recommend you read it over to get an idea of the process, and when you’re finished, go ahead and close to proceed with the remaining steps.

 

The first page you see when you create a report is Add Content. For what we’re doing, there really are only two options, Custom Chart and Custom Table. Since we’re going to use the example from the previous post, let’s select the “Custom Table” option.

 

 

Once selected, you are asked to add your data source. This is where our SWQL magic comes into play; change the Selection Method from “Dynamic Query Builder” to “Advanced Database Query (SQL, SWQL)”:

 

 

Make sure the radio button is set to “SWQL” so Orion knows which language to construct from. From here, let’s copy and paste our query from the previous post into the big box. Then click on “Preview Results” to make sure everything is working correctly. Note: this will not show you all results, just a small selection:

 

 

Once that’s complete, click “Add to Layout,” which will automatically send you to the custom table formatting table. Here you can add the columns from your query into the report:

 

 

 

Now that your columns are added, you’ll see the table layout section. Use the dotted icons to arrange the columns in any order you want (1). You can also expand on the column to change their name (2) to something friendlier, such as “System Name” instead of “Caption” (3):

 

 

Once you’re finished, click “Submit” to finish the table. If you wish to add more tables, or if you charted something, you can add more resources to the page. Otherwise, you can finish your report as you would normally.

 

That wraps it up for now. Next time we’ll continue exploring how to apply SWQL queries into widgets in the dashboard. Thanks for reading!

animelov

SWQL Walkthrough

Posted by animelov Employee Mar 13, 2018

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),

 

that will get me an output that looks like this:

 

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:

 

 

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:

 

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:

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:

 

 

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:

 

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:

 

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

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

… 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

 

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!

Filter Blog

By date: By tag:

SolarWinds uses cookies on its websites to make your online experience easier and better. By using our website, you consent to our use of cookies. For more information on cookies, see our cookie policy.