How does one combine nodes and interfaces using SWQL in a modern dashboard?

nick.f

My team has requested something along the lines of an "Interfaces with High Percent Utilization" report (or other bandwidth usage reports). In the classic reporting system, this was easily enough done because it was a pre-built template, but using the SWQL language in the modern dashboards has somewhat eluded me. I understand how to make modules based on a single type of item, such as a node or an interface, but I'm not understanding how to combine the two to show something like the following:

Node | Interface | Receive | Transmit

That's just a basic example, but I feel like if I can get to understand how to build that, I can work from there. Unfortunately, I also haven't noticed many good ways in SWQL of capturing bandwidth usage, whether by percent or otherwise. I know that someone else on my team wanted a report of, say, max used bandwidth, but using the SWQL Studio, I didn't see a good usage of that as well. I know I've asked a lot, but I appreciate any assistance I can get towards learning this better! Thank you!

KMSigma.SWI

Avoiding Unnecessary JOINs

Let’s say we want to get a list of all Nodes and their monitored interfaces for all Cisco devices in my infrastructure.

The Traditional (SQL) Way

SELECT [Nodes].Caption AS [Node]
     , [Nodes].IPAddress
     , [Nodes].Vendor
     , [Interfaces].Caption AS [Interface]
FROM Orion.Nodes AS [Nodes]
INNER JOIN Orion.NPM.Interfaces AS [Interfaces]
  ON [Nodes].NodeID = [Interfaces].NodeID
WHERE [Nodes].Vendor = 'Cisco'

We will query the nodes entity and connect it with the interfaces entity via a unique identifier (NodeID) and then pull information from both.

Since the Orion API already knows that these two entities are related, you can skip the JOIN and defining the unique key.

Using Navigation Properties

SELECT [Nodes].Caption AS [Node]
     , [Nodes].IPAddress
     , [Nodes].Vendor
     , [Nodes].Interfaces.Caption AS [Interface]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Vendor = 'Cisco'

Functionally, the above query is identical to the traditional query.  Each is easy to read and understand what’s taking place, but the value of navigation properties is when you link across multiple tables.

Logically, the next point of data we would want might be in and out bits per second.  In the traditional way, you’d have to join the Interfaces table to the Interfaces history table, but using navigation properties, we can just jump over to that next table.