Network & Systems Management Solutions Providers

Blog

12 May

By Antonis Athanasiou

SolarWinds Orion Custom Query Resource

Orion platform has a lot of resources you can place in your views (more than 250 in a single 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:

Add Custom Query Resource

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
 

Simple Query Output

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.

Routing Data Custom Query

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

Nodes with Problems Example

Make sure to check our future posts for more SWQL and Custom Query examples!

18 Sept5 Day Administrator Course for SolarWinds

Training covers all core Orion products & SCP Exam…

20 Nov5 Day Administrator Course for SolarWinds

Training covers all core Orion products & SCP Exam…