Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Super SWQL Query Required - Clean up Dashboard for 100+ Sites

Hi All,

We have around 100 sites in our SolarWinds NPM and the Dashboard is now starting to look a little cluttered with various SQL filters i have added for our device types.

I have downloaded the OrionSDK and have been playing with the queries but honestly I am a novice here and could really do with some help with what I am trying to acheive, which in turn will hopefully assist others in the future if they have similar requirements.

I have created quite a few custom properties within NPM for the nodes and two of these would be what I'd plan on using for the most part with filtering for each of the device types;

SW_Site - Site name assigned to a node (e.g. Site 1)

SW_DeviceType - Firewall, SD-WAN, Switch, Access Point, Printer, Server

The below is ideally what I’d like to achieve via a custom table where a single LED represents the health of a particular Device Type (SW_DeviceType) for a given site (SW_Site), and ideallly with the option to hover over an LED to see the status of all devices within that category (e.g. hover over Swiches and these are all listed with health/ LED status).

Site	Firewalls	Switches	Access Points	Printers	Servers
site 1	Green LED	Green LED	Amber LED	Red LED	Green LED
Site 2	Green LED	Green LED	Green LED	Green LED	Green LED
Site 3	Green LED	Green LED	Green LED	Green LED	Green LED

If that's achievable the final step would be to place those sites with a greater number of RED status's at the top of the list?

Hopefully thats enough info this far, let me know if further detail is required?

Gary

Find more posts tagged with

Accepted answers

All comments

marcrobinson

Because you are reporting status across all devices of a given type (sw_device) at each location (sw_site) - take a look at dynamic groups (easier to maintain than static groups) based on the custom properties that you defined. You can create your reports based on the group status - possibly with the web reports even. I think the sort by option could work. The default group pages may provide what you initially need for reporting on each site/device type. You can create dynamic groups based on more than one custom property too.

mesverrum

I made this post a few years ago with an example SWQL query that's a roughly similar concept to what you are asking.

https://thwack.solarwinds.com/t5/User-Experience/Show-us-your-Network-Atlas-maps/gpm-p/301531#M473

For specifically your case i'd do something like start from a list of distinct values on my site property, then join that back to the nodes where devicetype = 'firewall', etc. Then you'd have to cook up some kind of logic for how you want to aggregate their statuses. The simplest idea being a case when the count of nodes that are up is equal to the total count for that site and type then you use the up icon, if the up nodes is 0 then use the down icon, and for everything else use the warning indicator (you can get as fancy as you want with stacking up a mountain of cases if you really wanted though). For the order you could do another case statement just checking against the icons, sum(case when icon = down.gif then 2, when icon = warning.gif 1, else 0 end ) and get yourself a relatively easy priority scheme. Or you just use the groups like someone else mentioned but if you learn SQL/SWQL things like this are actually pretty fast to knock together.

This post also shows a pretty good example of the exact code to do prioritized dashboards,
https://thwack.solarwinds.com/t5/NPM-Documents/Alert-Prioritising-Dashboard-SWQL-for-Problematic-Nodes-Servers/ta-p/530412

gdsw

Thanks for both of your replies, I'm still working on this so hopefully I'll be able to share this if I ever get it working as required.