An Adventure Into Modern Dashboarding: Introduction to SWQL Studio
When the Modern Dashboard feature was introduced with the Orion Platform 2020.2 release, like many others, I got excited. The idea of having new widgets to tell a better story to my users was great. I made the same enthusiastic mistake everyone makes—I sent my directors the link to the Modern Dashboard page, so they could see the new hotness before I read all the details. Then I finished reading the details, and I saw *gulp* SWQL queries. It’s only a matter of time before I get the “when can we have something like this?” question, so it was time for me to tuck in and start learning.
I’m sure I’m not the only monitoring engineer who had these thoughts about learning SWQL. I was nervous because I’ve never pretended to be anything other than a SysAdmin/Monitoring Engineer, and this topic seemed to get dangerously close to programming and database queries. Thankfully, the THWACK community came to assistance! Some of my friends in the community (like shuth and KMSigma.SWI) were there to offer assistance, and the Orion SDK forum was there to peruse for examples and to ask questions. (Side note: if you aren’t sending friend requests to people on THWACK, so you can use the private message feature, you’re missing out.)
I work for an incredibly large retailer. We have hundreds of brick-and-mortar stores around the planet and a top-notch online store as well. Due to the pandemic, with many physical sites being closed for the safety of our employees and customers, more and more of the company’s livelihood has relied on online sales. In the past, unscheduled down time should always be avoided, but now it’s completely unacceptable.
Data is key to understanding what’s happening within our environment and, over my tenure with the company, more and more attention has been given to monitoring our infrastructure. Prior to my involvement, the management of the SolarWinds Orion Platform tools was one of those “when you get time” tasks handed to whatever team had a member free for a minute. It ran, and it worked, but there were few features being used to their fullest extent. Enter me. I quickly became “that guy” since my role was primarily monitoring and other responsibilities being secondary. This is a double-edged sword: our monitoring infrastructure is better than ever; but everyone is now looking directly to me. It’s my job to deliver.
After reading the release notes, browsing through the Product Blog examples, and reviewing an excellent example on the Orion Platform public demo, I knew I just needed to take off the water wings and jump in the deep end of the pool.
If you plan on following along with my journey, there are a few things I want to explain before we get started. I’m going to be using actual examples that work in either my production or my development environment. I have the same Custom Properties defined in each system, but being custom to my environment, they’ll be different than those defined in your system. You may need to tweak some queries to work for your environment.
Most of my queries are tested in SWQL Studio before I even think about putting them on a Modern Dashboard. SWQL Studio doesn’t need to be installed on your Orion server, you can just install it on your Windows workstation (or another server). It only needs to have a connection to the Orion server.
I’m also a fan of using Notepad++ with the Poor Man’s T-SQL Formatter plugin to format my queries for consistency. SWQL doesn’t care about line breaks or spaces but using them makes it easier for humans (and me) to read.
As stated in the first point, my examples come from either my production or development installs, so some information in screenshots has been redacted to protect the innocent.
Suffice it to say, there exists better documentation on using SWQL Studio and how to interface with the SolarWinds API. For the purposes of this post, I’m only going to hit the high points.
After I decide what information I want, I look for a good table within SWQL Studio. Since SWQL Studio looks and behaves much like SQL Server Management Studio (SSMS), I’m at least familiar with the layout. The top level is server, then there are namespaces (table grouped around similar data), then tables. One benefit SWQL Studio has over SSMS is the search field at the top of the Object Explorer pane, so I can search for a table/field if I don’t know where that data exists. You can either use the built-in search or just click around to explore. I’ve found myself doing both at times.
Since most monitoring within the Orion Platform is based around the idea of a Node, that’s where I’ll start. I expand the server, then the “Orion” namespace, and find “Orion.Nodes.”
To get the contents of that table, I can right-click on the table and choose “Generate Select Statement”
In a Query tab, I’m presented this:
SELECT TOP 1000 NodeID, ObjectSubType, IPAddress, IPAddressType, DynamicIP, Caption, NodeDescription, Description, DNS, SysName, Vendor, SysObjectID, Location, Contact, VendorIcon, Icon, Status, PolledStatus, StatusLED, StatusDescription, NodeStatusRootCause, NodeStatusRootCauseWithLinks, CustomStatus, IOSImage, IOSVersion, GroupStatus, StatusIcon, LastBoot, SystemUpTime, ResponseTime, PercentLoss, AvgResponseTime, MinResponseTime, MaxResponseTime, CPUCount, CPULoad, MemoryUsed, LoadAverage1, LoadAverage5, LoadAverage15, MemoryAvailable, PercentMemoryUsed, PercentMemoryAvailable, LastSync, LastSystemUpTimePollUtc, MachineType, IsServer, Severity, UiSeverity, ChildStatus, Allow64BitCounters, AgentPort, TotalMemory, CMTS, CustomPollerLastStatisticsPoll, CustomPollerLastStatisticsPollSuccess, SNMPVersion, PollInterval, EngineID, RediscoveryInterval, NextPoll, NextRediscovery, StatCollection, External, Community, RWCommunity, IP, IP_Address, IPAddressGUID, NodeName, BlockUntil, BufferNoMemThisHour, BufferNoMemToday, BufferSmMissThisHour, BufferSmMissToday, BufferMdMissThisHour, BufferMdMissToday, BufferBgMissThisHour, BufferBgMissToday, BufferLgMissThisHour, BufferLgMissToday, BufferHgMissThisHour, BufferHgMissToday, OrionIdPrefix, OrionIdColumn, SkippedPollingCycles, MinutesSinceLastSync, EntityType, DetailsUrl, DisplayName, Category, IsOrionServer FROM Orion.Nodes
At first glance, that looks like a huge bundle of text, but it’s easy to understand. All queries in SWQL Studio are “show me” (“SELECT”) and in this case, we’re asking only for the first 1,000 records (TOP 1000). This is the default by design because some tables can have millions of rows, and we don’t want to overly tax the system as we explore. Then it’s a comma-separated list of the fields available in this table. At the end, we have the table from which we want to pull these fields (FROM Orion.Nodes).
In my opinion, starting with all fields is the best place to start with any query. After you see everything that’s available, it’s easier to pare down the list of fields you desire to tailor for your exact needs.
We’re going to start with something simple and build from there. I want the node’s caption (how it’s displayed in the Orion Platform) and the most recent CPU load. If we work from the above query, we can remove everything from the list except for Caption and CPULoad. I’m also going to remove the TOP 1000, because I want to see the full list of nodes. That’ll turn the query into:
SELECT Caption, CPULoad FROM Orion.Nodes
The one thing KMSigma.SWI showed me and what I carry forward is every field should be on its own line. This is not necessary for the query to run, but it makes it easier for me to interpret. I also move the comma to the beginning of the line and indent the new line a few spaces to get the list to line up visually. I find this easier because if I want to exclude a field later, I can just comment it out (with two dashes). Refactoring it in this way gives me this query:
SELECT Caption , CPULoad FROM Orion.Nodes
Running it will result in a list of every node you’re monitoring (by name) and the current CPU Load:
The negative values are expected since those devices are monitored by ICMP (ping) only, so there are no statistics for CPULoad. In my case, I want to only show Windows machines, so we’ll need to add a filter. In the Orion Platform, “Windows” is a vendor (yes, we know the vendor is technically Microsoft, but go with me here). To filter your query results, you use a WHERE clause.
SELECT Caption , CPULoad FROM Orion.Nodes WHERE Vendor = 'Windows'
Rerun the query, and your list should only show those systems that are Windows-based systems.
One common practice is giving the items in the query simple, easy-to-understand names, so I want to mention aliasing. Say your users don’t understand what “Caption” means, so we can rename it by adding an “AS” statement. (In the strictest sense, the “AS” keyword is not necessary, but makes the query much more readable for others, so it’s a good habit.)
When we want to use an alias name and the name you desire contains a space character, it needs to be placed in brackets. For me, I use the brackets even when the name does not contain a space for the purposes of readability. Setting up your aliases gives you a query like so:
SELECT Caption AS [Node Name] , CPULoad AS [CPU Load] FROM Orion.Nodes WHERE Vendor = 'Windows'
Resulting in a query returning this table:
The data is the same, but the column headers have shifted to use the aliases.
One last thing that will save you time is aliasing the FROM location a simple name. Now I’m a real simple man, so I’ll rename Orion.Nodes to just “n.” Again, this won’t change anything in the results, just make it easier to read moving forward. It’s especially important to do this if you’re going to connect one table to another (more on that later). Get in the habit early!
It’s done identical to aliasing a field. Use the AS keyword and provide a new name you’d like to use. Be sure to go back up and statically identify your fields in the SELECT portion of the query. Again, this isn’t strictly necessary when you’re working with a single table but is a good habit to have. Remember, if there’s a space, you must use brackets, but you can use brackets even without spaces in the name as well. Doing this work converts our query into:
SELECT [n].Caption AS [Node Name] , [n].CPULoad AS [CPU Load] FROM Orion.Nodes AS [n] WHERE [n].Vendor = 'Windows'
Since dashboards are ultimately summary views, you’re going to want to summarize our data. These are done with aggregate functions. The only one I’ll use in my examples below is COUNT. There are others (MIN, MAX, SUM, AVG), but today we’ll just stick with the most basic example. If you return anything other than the results of the aggregate function, you’ll need to define how to organize the list based around the function (GROUP BY). Taking those few steps, it’s time to turn my attention to an actual production question I want to answer with a Modern Dashboard.
Let’s say I want all the applications I’m monitoring broken down by their status. After searching in the object explorer in SWQL Studio, I found the Orion.APM.Application table, which looks like it contains the data I want.
Using what we’ve already discussed, I’m going to query this table for the status, and I want to count the number of applications in said status.
SELECT COUNT( [APP].ApplicationID ) AS [Quantity] , [APP].Status AS [Status] FROM Orion.APM.Application AS [APP] GROUP BY [Status] ORDER BY [Status] DESC
You should be able to see I changed the name of the things we are counting. The first thing I’m counting is the number of the ApplicationIDs and calling them “Quantity.” You can rename it however you like, but I find having a name that makes sense will make finally creating the dashboard widgets easier when we get there.
The same was done with the Status. As far as putting your information into a specific order, “ASC” (the default if nothing is passed) means an ascending sort and “DESC” means a descending sort. With that query put into SWQL Studio, the results should look something like this:
You can see from the results that “status” is a numerical value. It’s a common lookup used by the Orion Platform across all products. If you really want to dig into the status, you can look at the Orion.StatusInfo table for more information, but for our needs at this point, we can leave the number as-is.
If you expand a table in SWQL Studio and scroll down far enough, you’ll see some fields that didn’t appear in your initial select all query. For example, under the Orion.Nodes table, you’ll find entries identified by a little “chain link” icon. These are called “Navigation Properties,” and they’re a way to link together the data between tables easily.
Simply put, this means if you start with Orion.Nodes, you can pull data from other tables by using these linked tables. If you’re familiar with traditional T-SQL, the same can be done with the JOIN operator, but we’ll only be looking at tables linked to the originally referenced table.
This is where renaming your original table comes in handy. Another widget I want to create will list the volumes on your nodes. Below, I’m not yet implementing a filter, but you can add whatever filter is applicable to your query. Since the Orion Platform already knows there’s a logical link between the Node and the Volumes on that Node, we don’t need to build our own links—just call the linked Navigation Property.
SELECT [n].Caption , [n].Status -- BEGIN Navigation Properties , [n].Volumes.VolumePercentAvailable , [n].Volumes.VolumePercentUsed , [n].Volumes.DeviceID , [n].Volumes.VolumeType -- END Navigation Properties FROM Orion.Nodes AS [n]
To use linked tables, you need to use an alias for the original table name ([n] in the above example). The syntax is [OriginalAlias].NavigationPropertyName.Field. Notice that we’re technically not making any calls directly to the Orion.Volumes table in our query—we’re using the native relationships defined in the Orion Platform to do this work for us. his saves us the headaches of building our own JOIN statements and makes it much easier for beginning Orion admins to see what’s happening.
Now that you understand how to retrieve the data and how to limit the results to give us the exact information we want, it’s time to convert the raw data into something pretty. In Part 2 of this series, I’ll show how I brought the sexy back to the data returned by these queries.
I’m using simple examples to help people get started. Are there any simple queries that you use that you’d like to share? Maybe us on THWACK can work together to get them into a pretty custom dashboard for you.