SolarWinds Orion Custom Query Resource – A Hidden Gem!

by 12, May, 2016Network Management

SolarWinds® Orion platform has a lot of resources you can place in your views (more than 250 in a single SolarWinds Network Performance Monitor (NPM) installation) and yet little is known of the infamous Custom Query resource. Whether you didn’t find what you were looking for or just feeling like going custom, the Custom Query resource is powerful enough to meet your needs!

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:

Orion
After adding the resource, click on the “Edit” button to define the SWQL query; We will start with a simple query, paste the following SWQL statement into the Query box and submit.

SELECT
RT.NodeID,
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
So we have our NodeID and interface names along with routing config for each node. Not bad as a start. in fact, we could achieve the same results using a custom table resource (a.k.a Web Report). The real power of custom query comes in pagination and the search box function! Let’s edit the resource again and see how we can achieve that; Replace the query with the following:

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
LEFT JOIN Orion.Nodes AS N on RT.NodeID=N.NodeID

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:

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}%'
)

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.

Orion

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

Training Course: SolarWinds Training Courses

Antonis Athanasiou

Antonis Athanasiou

Snr. Systems Engineer

Antonis Athanasiou is a Snr. Systems Engineer at Prosperon Networks. As a SolarWinds Engineer for over four years, Antonis has helped hundreds of customers meet their IT monitoring requirements with SolarWinds.

Training Course: SolarWinds Training Courses

Related Insights From The Prosperon Blog

Share This