In this follow up to “Orion SDK 101: Intro to PowerShell and Orion API,” Kevin M. Sparenberg, technical content manager for Community, will continue with his deep dive into the SolarWinds Query Language (SWQL). Kevin will show you how to represent existing data from within your monitoring ecosystem using traditional elements (e.g., reports, widgets, etc.) and in the new, modern dashboards, as well. A working knowledge of basic T-SQL queries is highly recommended.
In this session, you’ll learn:
- How to get started with dashboards
- What makes a good or bad dashboard
- How to build your own widgets
- How to execute proper dashboard research, design, and implementation
- Where to find more help
Episode Transcript
Welcome to SolarWinds Lab. I’m your host today, Kevin Sparenberg, and today we’re gonna be doing our SolarWinds Orion API 201 session. If you haven’t already watched the 101 session, I encourage you to go back and look at episode 86, where I go into detail about that, but today’s really gonna be all about dashboards. How do you use your custom queries? How do use your custom widgets? How to build reports and things you need to build dashboards that can make actionable decisions for your organization.
So there’s several things we’re gonna cover today, but I wanna start with kind of a quick agenda overview. So I wanna talk about how it gets started with dashboards. What makes it good or a bad dashboard, how to build your own widgets in the Orion Platform. How to actually have good conversations, research, design sessions with your teams. So you build the proper dashboards for their usage and also where to find more help, if you get yourself stuck. Back in Lab 86, I talked about SWQL or the SolarWinds Query Language, and I wanted to make sure I touch very briefly on it here. What it is is essentially a selectable SQL-like environment and language that you can use to interface directly with the data behind the SolarWinds API. Today, for my examples I’m gonna use SWQL Studio, which you can download as an MSI file from GitHub under the Orion SDK releases. So it’s just a basic interface. If you’re familiar with SQL Server Management Studio or SSMS, it basically looks and behaves very similarly. SWQL is very much like SQL in the fact that it uses a lot of the same techniques. So the same wildcards you use, the percent sign and the underscore and we support select queries only. Strings are defined by single quotes and comments are defined by double dashes.
In our last session, this is the query we started with, where we wanted to get a list of Windows nodes that are currently up and only return the caption, IP address, uptime, CPU count, sorted by the CPU count descending, and then uptime descending. If you already followed along with Lab 86, great, you probably already have this query somewhere in your Notepad somewhere. So go ahead and bring that out, but I’ll show it to everyone anyway. When we talk to customers about dashboards, whether it’s on THWACK or at an in-person event, we get a lot of kind of pushback on this because people think dashboards are built necessarily by the IT person or the monitoring person for the teams. And although that may be true in its purest sense, the information that people want on that dashboard comes from these other teams. So you need to have a sit down with those teams, first and foremost, you need to find out what information is most important to them. There’s only one thing I’m gonna say here. Everything is not a viable answer and everything dashboard is garbage and no one will be able to use it because it’s too busy. You need to understand how the information is going to be used. So is it going replace an existing thing? Is it going to be used for trouble tickets? Is it displayed in a dark NOC? You know, how’s it gonna be used in the environment? And last but not least, who is gonna be seeing this? Whether it’s someone consuming it, in like I said, a NOC or whether it’s gonna be the first page they land on when they log in to Orion. Or is it gonna be embedded in another source? So people can see it somewhere else. Those are all important questions. You have to have the answers to before you can actually start building these dashboards ’cause that’ll help you kind of build a scope in place. So you don’t get a lot of scope creep on either sides and the dashboard is built exactly as needed for the intended audience.
I wanna go ahead and show you a dashboard example. I never said it was going to be a good dashboard example. This is actually a horrible dashboard example for most people. It’s just too busy and doesn’t show the details necessary. The amount of information in the custom chart is just too volumous. There are too many interfaces. There’s no kind of cohesive overarching theme to this dashboard. This might be okay for you as the Orion admin and the kind of monitoring person in your environment. But realistically, if you were to give this to somebody, they’d be overwhelmed by the amount of information, you would really need to trim it down to make it useful for other parties. Now, hopefully you’ve established what people want on their dashboards. Now it’s a matter of getting to that data. And for me, I got that through SWQL Studio. So let’s go ahead and go down to Start and type S-W-Q-L and launch SWQL Studio, put in your server name, username, and password. Now that we’ve got SWQL Studio open and you can see it connect to your instance here. Let’s go ahead and start with that query we used last time. So there’s the query. I can hit F5 to run it and boom, there are my results. Perfect, exactly what we expected. Now, let’s say we wanted to take that information and actually put it into a dashboard or put it into a custom widget somewhere. There’s actually a really easy way to do that. So all you have to do is go to your Orion Platform and add a new widget type. So lemme show you how to do that. So here, I just have a summary view I built where I can go ahead and add things as I like. So we’ll go ahead and hit Edit. So on the page where you wanna put the widget, go ahead and hit Customize Page, hit Add Widgets, and search for query. There it is, Custom Query. And all you have to do is take this and drag it somewhere and drop it. And then you’re done adding your widgets and you’re done editing and we still have nothing there. So this is where we go Edit, and we’ll go ahead and call this Lab 91 example and we can just paste in the query and then submit. And there we go, the information we expected, perfect. So we know the query that works in the SWQL Studio can basically just be pasted into the custom query widget and get the information displayed a second time in a dashboard.
But although this is useful, it’s kind of ugly, right? So let’s take a couple of steps in prettying it up. There are two really important aliases to be used if you’re using SWQL and those two are LinkFor and IconFor. These are ways to actually take a specific query and actually make it prettier to bring in information, to bring in links and it can be done for any field or all the fields in a return set. So lemme show you a quick sample, how to use it. What I’m gonna do here is take the details URL and alias it as a LinkFor. So it really is as simple as putting an AS and then the LinkFor underscore field name after it. So let’s do it in SWQL Studio. So we can go ahead and add it to the return fields. So we want the details URL, and we want that aliased as _LinkFor_Caption. If we rerun this, you can see here is the _LinkFor_Caption. Now it looks ugly here, but let’s go ahead and put this back into the widget. So we’ll copy that entire content. We’ll go here, we will re-edit our widget. We will replace everything and we will submit. And it doesn’t look like a major change, but if you notice here, all of the captions are now links, and if you hover over them, you can actually get the details of the underlying node or element. This is fantastic if you need to do simple things from this particular page, if you have a dashboard like this you’re working with, and maybe you need to put a node into an unmanaged state for a while or delete it or edit it, you don’t have to actually go through the edit node process. You don’t have to go to another page. You can navigate directly to those changes from here. Now this result isn’t bad, but it could do a little bit more housekeeping. So let’s spruce it up a little bit for an IconFor. So for an IconFor, we need to find some type of graphic representation. So let’s go back into SWQL Studio and we’ll bring out the vendor icon. So here I’m adding in the vendor icon information, which you can get through a linked property or [Nodes].VendorInfo.Icon. And I’m actually gonna drop the URI because it’s not pertinent to actually building widgets. It’s really only used for add/remove kind of work within the API proper, but in SWQL it’s not terribly useful for displaying things. So let’s go ahead and run this, see what we get. And there we go. Now we have the vendor icon and for windows, this happens to be a GIF image and it’s 311. So you can see under icon, it says 311.gif. No problem. So let’s go ahead and put that into our custom query widget. Copy it all. Edit out. Forgot to add AS [_IconFor_caption]. Great, but something’s not right. If you notice here, they’re all broken images and that’s because natively the SWQL Studio and the custom query doesn’t actually understand where this image actually exists in the environment. Now you can find this really easily just by hovering over the image in any browser that you wanna use. So I can just go ahead and kind of steal that full URL and use that. So let me show you where I do that. So back in SWQL Studio, all you need to do is use this string, /NetPerfMon/images/Vendors and then tack on the vendor info icon at the end. And for the sake of completion, I’m gonna go ahead and put as icon for caption, that way it’s just easier to copy paste. So let’s go ahead and run it and make sure we return results. We do, perfect. If I go ahead and select all of this, copy that, and then we can edit once again, paste it in, and submit. And there we have it. We’ve got our icons in there as the icon for the caption, notice the icon for caption. So this is really great and it’s one of the things I like.
Now, our example here is not super interesting because, we already filtered everything for Windows. So we’re only gonna ever see Windows devices in here, but you’re not restricted to just the vendor icon. Although it’s one I use frequently because I like to get a quick visual understanding of what I’m looking at. What I also like to do is use the status icon, or even better, use this for multiple places. And let me show you what that would look like in a real brief idea. So let’s go ahead and go back to SWQL Studio. So not that I need it, but maybe I wanted the IP address also to be clickable. And I wanted to use the detail URL for that. Basically take me to the same place it would take if I clicked on the caption. You can do that really quickly and easily by just copying that line and doing link for IP address. And this is where the power of a custom query can really be ramped up. Because if you have a custom widget that shows maybe a node and an interface or an application and a component, you can have either separate links or the same links going back to different pages. So if we were to put this in place. And there we go, because we have the link for property on both the caption and the IP address, both of them are now both links and both support the hover over capabilities. So we’ve got a pretty resource. The problem is that resource is everything. It shows all of the Windows servers in our environment because that’s the only filter we have in place. Now that’s okay as you’re kinda building up to these, but if you were to put this on a dashboard and you have, I don’t know, a thousand, 10,000, a hundred thousand Windows servers you’re watching. This is basically a useless resources. You’d have to page through hundreds, if not thousands of pages, or you could say list all, and the query would take forever to return. When we talked earlier about what you should include on a dashboard, we said that the everything option is really not an option. So to do that, we use a where clause, and just like SWQL, we can use the where clause the same way it’s defined for SQL. So let’s go ahead and put one in there for things that haven’t been rebooted in a number of days, we’re just gonna go with 90 as an example. So in SWQL Studio, we’re gonna add an additional where clause. So right now we have the vendor is Windows. It is currently up and let’s add another one. And this one’s for system uptime. Now the thing about system uptime is it’s stored in seconds because that’s how frequently the polling happens. We wanna make sure we have the most important information. So if we’re talking to compare it to 90 days, first, we got to convert seconds to minutes, minutes to hours, hours, to days, and then compare that to 90. It’s really not that hard, it’s 60 seconds in a minute, 60 minutes in an hour, 24 hours in a day. So this is now giving us the total number of days. And we’re looking for things where the total number of days are larger than 90. So the devices haven’t been rebooted in 90 days. So let’s go ahead and run this and make sure we get results. We got information back and we only got 22 rows this time as opposed to our entire infrastructure. So let’s go ahead and copy it all. Go back to our widget and paste. Okay, so you can see we’re only getting five pages back now for a total of 22 objects. This is the kind of stuff you’re looking for. Obviously add more filters as necessary. You can filter on any number of things. It can be caption, it can be uptime. It can be the vendor like we’ve already done. It could be that it has child elements or meets a specific criteria, like has a custom property equal to this or to that. All of these are options that you can actually do in SWQL Studio. And as long as the content renders in SWQL Studio, it’ll almost certainly render cleanly in a custom query widget.
Now, if you were taking this and putting this directly on the dashboard, there’s one more thing I would add and that’s kind of a subtitle or a descriptive text. So again, just go back into Edit. And normally in here is just kind of where I described my where clauses. So, “Up” Windows servers online more than 90 days. And I know that seems like a really small thing, but when people are trying to consume these widgets and understand what’s actually on this dashboard, it’s helpful to have that little extra bit of information. So I don’t know if anyone’s actually taken the time to look at the new reporting engine as in depth as I have, but I know there was a lab earlier with Chrystal and Leon talking about reporting. And I wanna show you one thing that you can use your now acquired SWQL skills to do within reporting. So we’ll go ahead and go to reports. All reports. We’ll go to manage reports. And let’s go ahead and create a new report. So let’s go ahead and just build a custom table. And for the table type, we’re gonna go ahead and select Advanced Database Query. We will select SWQL and I’ll actually use the original query we started out with today, and then I can preview the results. And then we’ve got a whole bunch of information. This is what we kind of expected, and then we should give it a good name. And we’ll add it to the layout. And now this is the page where we actually choose what’s going to be in that table. So we’ll go ahead and do add columns. And the important ones for me are probably the caption. You know what, forget that, they’re all important to me. We’re gonna add all of them except the URI. And then we can go ahead and preview that resource, the information like we want, perfect. And then we can also sort our results. Now the query we pasted in there had sorting, but this sorting here will override that. So if I remember right, it was CPU count, descending and then uptime ascending. And let’s go ahead and submit that, we’ll give our report a quick title, and our preview, perfect, next, next, and finish.
Now, this is just one table in one report, but what I like to do for these, especially if I’m already in the mindset of dashboarding, is I will build a report that includes multiple of queries like this. So I can pull in the information from their dashboard and give them the information all in one report, because maybe the manager of that team doesn’t actually want to, you know, go to the Orion page every day. That’s fine, totally understandable. Everyone works differently, but maybe having a weekly report about that dashboard and the health of all the contents there would be useful. These are the things you get by having those conversations with the teams that you build the dashboards for. We’re getting a lot of information from SWQL Studio using the language to put things into reports and dashboards. And that’s great, but don’t overlook the ability to put non-SWQL things in there as well. So there’s three major kind of categories for this for me, the first one is custom HTML resources. And these are resources if you’re fluent with HTML, go ahead and write them yourself, no problem. Or you can totally steal them from other parties, especially ones that offer things like weather maps or charting engines or something like that. You can just paste in the HTML and you’re probably gonna be okay. The second one is gonna be Orion Maps. Because of the new mapping engine, you can actually take this information and put that right on a page. Your map may already be filtered to what you want and if that’s the case, perfect, build it, save it, put it on a page. And last but not least, is PerfStack. If you built a PerfStack chart for a specific purpose, like for one of these teams, it’s an excellent way to use that resource without them having to go into the performance analysis area and find it themselves, just put it right on their dashboard. You can see that this particular dashboard is all of those things. This is a custom HTML resource that uses some JavaScript that I found somewhere on the web. I can’t even remember anymore. This is a new modern map, which is just a location summary, which just shows overall status of my four locations in my demo lab. This is a custom HTML resource that I wrote by hand, does not use JavaScript, it’s just plain HTML. We have one of the new modern Orion Maps where we actually show circuit status, where we connect things, where we don’t actually have input on the far side, really, really nice feature in the new Orion Maps. And then we have here our PerfStack chart, which shows us our WAN discards. And these are really easy to add. You just go add widget and let’s say, we’re looking for a map. And then we search, and we’re looking for an Orion Map, drag drop, done, done, choose the map. Let’s say we’re interested in Active Directory. And there we go. There’s the status of our Active Directory environment. So you can see you don’t actually have to use kind of like dev skills and SWQL to actually pull in good information for a dashboard. Sometimes you can just look on THWACK for inspiration. You can build your maps, build your PerfStack charts, and maybe just assembling them is enough. But I wanted to make sure you had the SWQL skills necessary, if you need it to do something very, very custom for a specific dashboard.
Now, speaking of custom dashboards, I need to talk at least briefly about the modern dashboards on the new release of Orion. In the 2020.2 release of the Orion Platform, we’ve added modern dashboarding, and you can find that by going to settings and manage dashboards. So I’m only gonna briefly touch on modern dashboards because there’s a lot of things you can do with it. And I just wanna give you the very basics. We are probably gonna have, in a session later about them. If it’s a lab that you want, please let us know in the comments. It’s something we can look to get out there, but to start, I’m just gonna show you where you can take your existing SWQL skills that you just learned and apply them to the dashboards. So if you need to build a new modern dashboard, you can click Add modern dashboard, or since I’ve already built one, I’m gonna go ahead and go into that one. Now all three of these particular resources can be resized, dragged, changed the colors, and all that kind of stuff. These are all built on the same query we were using earlier. In fact, if you look behind the scenes, you can look at the data model here, and this is pretty much the query we’ve been running with. The only difference here is it looks for the Windows servers that are up and also looks for the ones that are over 90 days online. And then it returns a count instead of returning details. The second tabular style resource shows the same kind of thing. Again, you can go ahead and look at the custom SWQL on the backside, there it is. Same thing we’ve been working with before, and last but not least is all the Windows servers with uptime over 90 days. This one just happens to be grouped by a specific feature. In this case, it happens to be the status again, custom SWQL. So the SWQL skills you learn today can be used both on the classic and the modern dashboards. We wanna make sure that you can have a skill that transitions through the entire platform as we make changes and add to it.
So hopefully I’ve given you some information you need to kind of take your first steps in building your custom dashboards and maybe your custom reports and maybe working with modern dashboards, which is great. Now for us here at SolarWinds, we’re always trying to give you the best information possible, but we rely on your feedback to determine what we’re gonna be making. Now, I made a call out earlier to an earlier Lab session that I had done, which was Lab 86. And that was the 101 version of this, but this is Lab 91. We have 90 episodes before this of fantastic content. And most of it is still relevant today for every monitoring engineer. So please don’t be afraid to go into the archives and look around a little bit, but for future episodes, we’re always looking for feedback. I mean, did I cover everything that you wanted to see? Maybe I covered 80%. What’s that 20% I’m missing? Let me know, let us all know. And we’ll try to get that into the next SolarWinds Lab. Thank you for joining me.
