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!