Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

I give up on SWQL Joins!!

I cannot figure out how to construct a SWQL JOIN to save my life. I am even using the SWQL Studio but can't get the syntax right. I've searched and I cannot find any good references or tutorials. Lots of references on WHAT it can do, but none so far on HOW TO DO IT!!

Using a simple example of the Nodes table and the Nodes Custom Properties table, I am trying to build a query that includes only nodes with a certain value for a node custom property. Can anyone offer any help or advice on how to understand the structure of JOINS in SWQL?

Any guidance would be so appreciated.

Find more posts tagged with

Accepted answers

KMSigma.SWI

In most cases, you can skip using JOINs entirely. I wrote about it in the SDK forum.

All comments

bobmarley

Here is how to join Nodes with CP's. Replace group, severity, description, application with your own custom property names. Afterwards you can use a where clause such as

Where GROUP LIKE '%TheIntelGroup%' or Where Caption LIKE '%MyServersName%'

to select specific items

--IP_Address, Caption, NodeID is from the Nodes Table
--Group, Severity, Description, Application is from the Custom Props Table
Select IP_Address, Caption, NodeID, _Group, _Severity, _Description, _Application FROM Orion.NodesCustomProperties VariableNameForCustomProperties


INNER JOIN Orion.nodes KeyYouWantToJoinWith on VariableNameForCustomProperties.NodeID = KeyYouWantToJoinWith.NodeID

patriot

I'm not following your reply. Please show me an actual complete query that references a node custom property called, for instance "Owner".

bobmarley

Select IP_Address, Caption, NodeID, Owner FROM Orion.NodesCustomProperties VariableNameForCustomProperties

INNER JOIN Orion.nodes KeyYouWantToJoinWith on VariableNameForCustomProperties.NodeID = KeyYouWantToJoinWith.NodeID

patriot

I'm still not interpreting things correctly, because I typed this and it does not work:

Select IP_Address, Caption, NodeID, Owner FROM Orion.NodesCustomProperties C INNER JOIN Orion.Nodes NodeID on C.NodeID = NodeID.NodeID

KMSigma.SWI

In most cases, you can skip using JOINs entirely. I wrote about it in the SDK forum.

wluther

@patriot This took me a while to wrap my brain around when I started too, so no worries, you're not the only one.

For your specific example, joining the Nodes table (Orion.Nodes) to the NodesCustomProperties (Orion.NodesCustomProperties) can actually be accomplished with an implicit join, which is far less work. An implicit JOIN in SWQL is kind of like connect the dots, so to speak.

The way I usually write it, for the most part, is starting from Orion.Nodes, and then jumping over to Orion.NodesCustomProperties, but you can do it the other way around too. Within the SDK, you should see some fields with a little chain icon next to them, once you've expanded that table out.

FROM Orion.NodesCustomProperties AS ncp

Now, any field we call from the NodesCustomProperties table will need to have the alias prefix attached, such as:

SELECT
ncp.Owner
FROM Orion.NodesCustomProperties AS ncp

Now you can jump directly into the Orion.Nodes table by appending ".Node." after your ncp prefix. (You don't need the leading "Orion.", other than your initial FROM)

SELECT
ncp.Owner
,ncp.Node.Caption
FROM Orion.NodesCustomProperties AS ncp

Anything you want to show from the custom properties table will begin with ncp., and anything from the Nodes table will begin with ncp.Node..

Sometimes a few different types of examples, or even the same ones explained by different folks, and in different ways, works. At least that's how it worked for me. I must have read through all there was to find, and then it all just clicked after I saw the same thing being said with a different example.

Let us know how it goes, and if you need anything else.

Thank you,

-Will

sum_giais

[quote userid="99062" url="~/product-forums/the-orion-platform/f/report-lab/96076/i-give-up-on-swql-joins/303174#303174"]An implicit JOIN in SWQL is kind of like connect the dots, so to speak[/quote]

Well said @wluther!

Just to expand on that, SWQL converts everything to SQL under the hood, and using the dot notation (Navigation Properties as they're commonly referred) the SWIS API is essentially masking the work of performing a join within SQL, which is represented by the chains within SWQL Studio you see.

Hang in there @patriot.

I will add though, if you haven't taken a look at the great content @KMSigma.SWI linked to above its well worth it.

patriot

Thanks to all who contributed. The KMSigma blog about Navigation Properties really helped. Now, on to the next aspect of SWQL queries - date/time formatting. One step at a time.

KMSigma.SWI

I feel like @patriot is writing a class curriculum.

Professor Patriot:
I'm unable to turn in my assignment for September because my router ate it. Can I have an extension?
Sincerely,
Eric Withakay

bobmarley

I was trying to show him the 'Long Math' method first (if anyone here even knows what that is)

stuartd

I've been trying to get together a group of S(W)QL novices to see if we can create a study group (or similar idea). Full thread is here: https://thwack.solarwinds.com/product-forums/the-orion-platform/f/orion-sdk/91251/swql-study-group-for-the-complete-novice---thoughts - ultimately it turns out @KMSigma.SWI is writing a book that may be the answer to our headaches

Bottom line is that I haven't managed to get that study group going (even though I still think it a good idea), but let me say you are not alone in your efforts. I very much struggled with JOINs and now have them sorted. Now I still struggle a bit with implied or implicit JOINs, or more specifically where I've found a query that I need to add to make useful in our environment. It's a nightmare, and then you get to your situation where you master one thing and then you realise you need to know how to do dates, or extract the most recent item, or organise the columns or ...and so on it goes.

Anyway, if interested, please go throw your name in the hat on my thread and I'll see what can be done to take it forward.