Enhancing Custom Query Widgets

The custom query widget is one of the most flexible extensions for Classic Dashboards, allowing you to present your data in any way you see fit.  The data represented can be anything stored within the SolarWinds Platform, but for the sake of examples, we'll begin with the simple scenario where you want to show all of your "Up" nodes and their CPU utilization.

Our Starting Query

We'll start with a simple query asking for the "up" nodes and their current CPU Utilization.

SELECT [Nodes].DisplayName
     , [Nodes].CPULoad
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Status = 1 -- a Status of 1 indicates 'Up' (See Orion.StatusInfo for a complete list)
  AND [Nodes].External = False -- (Not External)
  AND [Nodes].ObjectSubType <> 'ICMP' -- (Not ICMP)
  -- some very specific devices do not report their CPU, so we'll skip them.
  AND ( [Nodes].CPUCount IS NOT NULL 
     OR [Nodes].CPULoad < 0
      ) 
-- We'll sort by Caption/DisplayName because it's convenient
ORDER BY [Nodes].DisplayName

Placing the above query in a custom query widget will product output based on your environment.

Extending with Icons via Macros

The data returned looks good but isn't very visually appealing.  It would be nicer if we could add a few icons- perhaps representing the status or the type of device.

Native to the SolarWinds Platform, there is a macro called _IconFor_ and it's used in our example below.

SELECT [Nodes].DisplayName
     , [Nodes].CPULoad
       -- add the icon for 'DisplayName' from the Vendor Icon
     , [Nodes].VendorIcon AS [_IconFor_DisplayName]
       -- the rest of the query does not change
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Status = 1 -- 1 = 'Up' (See Orion.StatusInfo for a complete list)
  AND [Nodes].External = False -- (Not External)
  AND [Nodes].ObjectSubType <> 'ICMP' -- (Not ICMP)
  -- some very specific devices do not report their CPU, so we'll skip them.
  AND [Nodes].CPUCount IS NOT NULL 
-- We'll sort by Caption/DisplayName because it's convenient
ORDER BY [Nodes].DisplayName

Which changes our displayed output to:

Well, that's less than helpful.  This is because the vendor icons aren't stored in the root of the website, they exist in a subfolder.  We'll need to prepend the icon name with the path to the folder containing these icons.  The easiest way to do this is to find a known good icon on another page and find the path. This can be done multiple way, but I've found the easiest to look at the code behind the page.

We don't want the full URL, just the relative URL, which for Vendor Icons is /NetPerfMon/Images/Vendors/<vendorIconFileName>.  We can update our query to include this.  This can be done with either the string addition operator (+) or concatenation function.

SELECT [Nodes].DisplayName
     , [Nodes].CPULoad
       -- add the icon for 'DisplayName' from the Vendor Icon
     , CONCAT('/NetPerfMon/Images/Vendors/', [Nodes].VendorIcon) AS [_IconFor_DisplayName]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Status = 1 -- 1 = 'Up' (See Orion.StatusInfo for a complete list)
  AND [Nodes].External = False -- (Not External)
  AND [Nodes].ObjectSubType <> 'ICMP' -- (Not ICMP)
  -- some very specific devices do not report their CPU, so we'll skip them.
  AND [Nodes].CPUCount IS NOT NULL 
-- We'll sort by Caption/DisplayName because it's convenient
ORDER BY [Nodes].DisplayName

After submitting that change, the custom query widget would render correctly.

There are many, many options available: Alert Status, Events, Volumes, Interfaces, and Node Status icons.  This is a table of some of the icons available for your disposal.

If there's an icon in the web console, you'd like to use, you just need to find the relative URL for the _IconFor_ macro and build it together.

Extending with Links via Macros

As nice as this listing is, it would be better if it was interactive. For this purpose, the Custom Query Widget has a _LinkFor_ macro we can utilize.  Let's link the Node's display name to the details page.

SELECT [Nodes].DisplayName
     , [Nodes].CPULoad
       -- add the icon for 'DisplayName' from the Vendor Icon
     , CONCAT('/NetPerfMon/Images/Vendors/', [Nodes].VendorIcon) AS [_IconFor_DisplayName]
       -- add a link to the node's details page
     , [Nodes].DetailsUrl AS [_LinkFor_DisplayName]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Status = 1 -- 1 = 'Up' (See Orion.StatusInfo for a complete list)
  AND [Nodes].External = False -- (Not External)
  AND [Nodes].ObjectSubType <> 'ICMP' -- (Not ICMP)
  -- some very specific devices do not report their CPU, so we'll skip them.
  AND [Nodes].CPUCount IS NOT NULL 
-- We'll sort by Caption/DisplayName because it's convenient
ORDER BY [Nodes].DisplayName

Most element types in the SolarWinds API have a DetailsUrl property, so we can bring it in very easily.  Now our Display Names are links and (BONUS!) support hover-over functionality.

Getting Specific

These options are great for "summary" pages, but what if we wanted a custom query widget for specific pages?  Many pages include "hidden" macros which identify the entity in view.  For nodes, you can use the variable ${NodeID} in your query and when the query is executed, that Node's ID is inserted into your query.

There are too many permutations to list them all here, but if you are interested in seeing one in action, you can download and review Group Membership (Node) from the Custom Queries Content Exchange.