Decreasing JOINs - the Power of Navigation Properties

Because many people (myself included) started to learn the syntax of SWQL Queries based on (sometimes limited) knowledge of SQL queries, I wanted to share with everyone the best way to avoid JOIN clauses.

The examples herein will be show in SWQL Studio, so if you don't already have SWQL Studio installed, you should do so if you'd like to follow along.  Some of my sample data from below is completely made up, but you should see something similar in your environment.

If you are not familiar with the traditional T-SQL JOIN clauses, there's multiple excellent articles on Microsoft's and other sites (Official Doc: Joins (SQL Server) - SQL Server | Microsoft Docs).  My non-technical description of a JOIN is "a way to connect one table to another through a shared element."

I will use terms like field and record and for the sake of clarity this is what I mean:

Sample Tables

Table 1: Selection of elements from Orion.Nodes

SELECT NodeID, Caption, ObjectSubType, DetailsURL FROM Orion.Nodes

Table 2: Selection of elements from Orion.NPM.Interfaces

SELECT NodeID, InterfaceID, InterfaceName, InterfaceAlas, TypeDescription, PercentUtil, DetailsURL FROM Orion.NPM.Interfaces

Moving the Queries to a more "readable" format

Converting the queries to something more readable helps me organize the fields and make the logical connection.  These are the same queries from above, but refactored.  For SQL and SWQL anything on a line after a double-dash is considered a comment.

-- Data from Orion.Nodes
-- Read the Comments in number order to understand the logic
--
SELECT [Nodes].NodeID          -- 2. Prepend the fields with the table alias.
     , [Nodes].Caption         -- 3. The same applies for all fields from this table.
     , [Nodes].ObjectSubType   -- 4. and again
     , [Nodes].DetailsURL      -- 5. you get the picture
FROM Orion.Nodes AS [Nodes]    -- 1. Choose an easy to comprehend alias for the table

-- Data from Orion.NPM.Interfaces
-- Read the comments in number order to understand the logic
--
SELECT [Interfaces].NodeID                -- 2. Prepend the fields with the table alias.
     , [Interfaces].InterfaceID           -- 3. The same applies for all fields from this table.
     , [Interfaces].InterfaceName         -- 4. and again
     , [Interfaces].InterfaceAlias        -- 5. lather
     , [Interfaces].TypeDescription       -- 6. rinse
     , [Interfaces].PercentUtil           -- 7. repeat
     , [Interfaces].DetailsURL            -- 8. you see where I'm going, right?
FROM Orion.NPM.Interfaces AS [Interfaces] -- 1. Choose an easy to comprehend alias for the table

Joining the Tables (Traditional)

Using the JOIN clause (which is 100% supported in SWQL), you can join these two tables on a common element (NodeID).

--
-- Read the Comments in number order to understand the logic
--
SELECT [Nodes].NodeID                           -- 3. Field from the original table
     , [Nodes].Caption                          -- 4. Another from the Nodes table
     , [Nodes].ObjectSubType                    -- 5. Yep, this one is also from Nodes
     , [Nodes].DetailsURL                       -- 6. Nodes as well
     , [Interfaces].NodeID                      -- 7. This is from our joined Interfaces table
     , [Interfaces].InterfaceID                 -- 8. Also from the Interfaces table
     , [Interfaces].InterfaceName               -- 9. More from Interfaces
     , [Interfaces].InterfaceAlias              -- 10. You can see why the alias...
     , [Interfaces].TypeDescription             -- 11. ... makes it easy to understand ...
     , [Interfaces].PercentUtil                 -- 12. ... the source of the field in question.
     , [Interfaces].DetailsURL                  -- 13. I ran out of clever things to say
FROM Orion.Nodes AS [Nodes]                     -- 1. Our original table
INNER JOIN Orion.NPM.Interfaces AS [Interfaces] -- 2. Our second table
  ON [Nodes].NodeID = [Interfaces].NodeID

Now each record will show all of the requested fields from both tables.

Notice the second reference to NodeID (the fifth field in the above record)?  Since our JOIN guarantees that the field NodeID and NodeID1 are equal, we could remove one of them.

But there's also a second reference to DetailsURL.  In this case, the Node's details link and that for the interface are different.  We should probably come up with a more logical way to present this.

--
-- Read the Comments in number order to understand the logic
--
SELECT [Nodes].NodeID
     , [Nodes].Caption
     , [Nodes].ObjectSubType
     , [Nodes].DetailsURL AS [NodeDetailsUrl]           -- 2. Provide a field alias to the field name
     -- , [Interfaces].NodeID                           -- 1. (commented out the duplicate NodeID because the JOIN already guarantees these match
     , [Interfaces].InterfaceID
     , [Interfaces].InterfaceName
     , [Interfaces].InterfaceAlias
     , [Interfaces].TypeDescription
     , [Interfaces].PercentUtil
     , [Interfaces].DetailsURL AS [InterfaceDetailsUrl] -- 3. Provide a different field alias for this other one
FROM Orion.Nodes AS [Nodes]
INNER JOIN Orion.NPM.Interfaces AS [Interfaces]
     ON [Nodes].NodeID = [Interfaces].NodeID

Neat, right?  Now we can join two tables on a matching record and show fields from either table in a single record.

But what about 3 tables?  What about 4, 5, 6, 11?  It can be done with multiple JOIN operations between the tables, but you can see that things could get complex, right?

The Easier Way - Navigation Properties

The incredibly smart people at SolarWinds Orion Architecture decided that although JOINs do work, you need to have an intrinsic knowledge of the connected tables before you can be successful.

Each table (technically called an "entity") in the SolarWinds Orion API can have up to 5 different types of elements.

• Primary Key - this is the unique identifier(s) for this table
• Fields - the things you can query
• Inherited Fields - additional things you can query (but aren't the default)
• Navigation Properties - connected entities <-- this is the focus of the discussion today
• Verbs - actions you can take on the elements in the entity (records in the table)

What is a Navigation Property?

The Official Definition: a property on one entity type whose value is a set of zero or more other entities of a particular type

The "KMSigma" Definition: a way to skip doing JOINs.

I'm going to zoom in on one specific navigation property under Orion.Nodes.

Looks familiar right?  Remember that this is a child of the Orion.Nodes entity, not of Orion.NPM.Interfaces.

Here's what it's saying in plain language: You can query for fields from Orion.Nodes and then connect to Orion.NPM.Interfaces via the Interfaces navigation property.

Taking our above examples, we can connect Orion.Nodes with Orion.NPM.Interfaces via the navigation property.  Starting simple, let's just include the interfaceID.

--
-- Read the Comments in number order to understand the logic
--
SELECT [Nodes].NodeID
     , [Nodes].Caption
     , [Nodes].ObjectSubType
     , [Nodes].DetailsURL
     , [Nodes].Interfaces.InterfaceID
--        ^        ^           ^
--        |        |           |
--        +- 1. Starting with Orion.Nodes
--                 |           |
--                 +- 2. Connect to Orion.NPM.Interfaces via the Navigation Property
--                             |
--                             + 3. Pull this field from Orion.NPM.Interfaces
FROM Orion.Nodes AS [Nodes] -- 1. Having a table alias here is practically mandatory to keep things stright in your head - get in the habit.

Huzzah!  We've connected Orion.Nodes to Orion.NPM.Interfaces without using a JOIN clause.

If we wanted to add the other fields, it's the same as following the formula.  Thankfully, SWQL Studio has autocomplete, so it's easy.

And the results are pretty much what you would expect.

But all of this was done WITH NO JOIN CLAUSES.

You can even continue further.  If you look at the Orion.NPM.Interfaces entity, it also has navigation properties.

--
-- Read the Comments in number order to understand the logic
--
SELECT [Nodes].NodeID
     , [Nodes].Caption
     , [Nodes].ObjectSubType
     , [Nodes].DetailsURL
     , [Nodes].Interfaces.InterfaceID
     , [Nodes].Interfaces.InterfaceName
     , [Nodes].Interfaces.InterfaceAlias
     , [Nodes].Interfaces.TypeDescription
     , [Nodes].Interfaces.PercentUtil
     , [Nodes].Interfaces.DetailsUrl
--     * Show two table hops *
     , [Nodes].Interfaces.ForecastCapacity.DaysToCapacityAvg
--        ^        ^           ^                 ^
--        |        |           |                 |
--        +- 1. Starting with Orion.Nodes        |
--                 |           |                 |
--                 +- 2. Connect to Orion.NPM.Interfaces via the Navigation Property
--                             |                 |
--                             + 3. Connect to Orion.NPM.InterfacesForecastCapacity via the Navigation Property on Orion.NPM.Interfaces
--                                               |
--                                               +- 4. Pull DaysToCapacity field from Orion.NPM.InterfacesForecastCapacity
FROM Orion.Nodes AS [Nodes] -- 1. Having a table alias here is practically mandatory to keep things stright in your head - get in the habit.

Summary

Navigation Properties are one of the most underappreciated features of the SolarWinds Orion API.  The API already knows how the various data types are connected, so use what's already been built for you.  Go forth and explore!