SolarWinds Orion Custom Query Resource – A Hidden Gem!

This resource uses the Semantic Web Query Language (SWQL) which is very similar to SQL as it supports all popular SQL constructs like GROUP BY, JOINs, CASE, etc. A benefit of SWQL is the shorthand nature, which reduces and often removes the need to create JOIN and UNION statements.

In this blog post we are going to use the Custom Query Resource to display routing information configured across all devices monitored in Orion. We are also going to utilize the search function within the resource to pin down easily a particular routing entry.

First, we start off by adding the custom query resource in a summary view:

Now we replaced the NodeID column with the actual Node Name, also included a Status LED icon and a clickable link which leads to the node details view. The Custom Query resource is clever enough to parse the [_IconFor_Column] and [_LinkFor_Column] entries and produce better results.

However, this is not enough yet. We would also like to search for specific Nodes, routing destinations or next-hop entries. Here’s how:

Edit the resource again, tick on the “enable search” checkbox and copy/paste the same query to the bottom textbox, but this time we will include a WHERE clause too. Note in the query the use of the %${SEARCH_STRING}$ variable, which will take the input of the search field to use in the query:
[code]SELECT
N.Caption AS [Node Name], ‘/Orion/images/StatusIcons/small-‘ + ToString(N.StatusIcon) AS [_IconFor_Node Name], ‘/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a’+ToString(N.NodeID) AS [_LinkFor_Node Name],
RT.InterfaceName AS [Interface Name],
RT.RouteDestination AS [Route Destination],
RT.RouteMaskLen AS [CIDR],
RT.RouteNextHop AS [Route Next Hop],
RT.Metric, RT.ProtocolName AS [Protocol Name]
FROM Orion.Routing.RoutingTable AS RT
INNER JOIN Orion.Nodes AS N on RT.NodeID=N.NodeID
WHERE
( N.Caption LIKE ‘%${SEARCH_STRING}%’
OR RT.RouteDestination LIKE ‘%${SEARCH_STRING}%’
OR RT.RouteNextHop LIKE ‘%${SEARCH_STRING}%’
)[/code]

Adding that ‘WHERE’ clause essentials makes it possible to filter our results based on a Node Name, routing Destination or Next Hop, so in this example searching for routing data by node or which nodes have a Route Destination of 10.0.2.0 is available. A search box now appears on the top right of the resource! Each column is also sortable to make things even easier.

If it can be SWQL’d, it can be displayed! Here is another example to summarize Nodes with problems together with some critical statistics: