Creating SWQL joins using the links in SWQL studio

I encourage you to read Kevin's response and follow the post he's linked. Just know, that even after having several very clever folks telling me, it took me a good while to finally see the light - so to speak.

In your example you could do this:

FROM Orion.Nodes AS nodes

Then anywhere you wanted to insert something from Orion.AlertActive you would simply type nodes.AlertActive.name_of_field_you_want

Another example that I was given, and made a One Note entry about so I can always refer back if I forget or have a dull day is:

Hope this helps.

Hi, 

Thank you both i will take a look at both and read up

Yes i met Kevin at SWUG London a few months back. Very knowledgeable guy indeed

I was gutted I couldn't make the London SWUG, but personal reasons (fostering) meant I couldn't justify leaving my wife for two days.

So I should have added - and sticking with your example, the bit you use to do the join AlertActive is outside of any brackets and this is your key JOIN word. The bit in brackets is the record you are actually JOINing to.

I think that simple aspect fried my head for a bit 

it was good but i missed the swql bit.

Can you only use common links with the same name from different tables?

IE in kevs page it mentions .interfaces from two different tables which link. In the example above i was trying to join active alerts

With this

SELECT [nodes].Caption, [nodes].NodeID, [nodes].ObjectSubType, [nodes].IPAddress, [nodes].IPAddressType, [nodes].DNS, [nodes].Vendor, [nodes].VendorIcon, [nodes].Icon, [nodes].Status, [nodes].AlertActive.AlertObjects

FROM Orion.Nodes AS nodes
where Caption LIKE'%srveucsc%'
ORDER BY Caption

 I get an error of 'entity Orion.nodes does not contain requested navigation property AlertActive

Thanks

it was good but i missed the swql bit.

Can you only use common links with the same name from different tables?

IE in kevs page it mentions .interfaces from two different tables which link. In the example above i was trying to join active alerts

With this

SELECT [nodes].Caption, [nodes].NodeID, [nodes].ObjectSubType, [nodes].IPAddress, [nodes].IPAddressType, [nodes].DNS, [nodes].Vendor, [nodes].VendorIcon, [nodes].Icon, [nodes].Status, [nodes].AlertActive.AlertObjects

FROM Orion.Nodes AS nodes
where Caption LIKE'%srveucsc%'
ORDER BY Caption

 I get an error of 'entity Orion.nodes does not contain requested navigation property AlertActive

Thanks

You can only "implicit" JOIN direct to any listed navigation property [NPs] that is listed under the table you are starting from. You can also connect to any other NP that the directly listed NP's have access to.

An example... 

Orion.Node has AccessPoints (or Orion.Packages.Wireless.AccessPoints) as its first NP. If you were to separately navigate to that table, you could also connect to 2 other NPs that Orion.Nodes doesn't have direct access to.

The easiest way to understand is to open SWQL Studio and expand the field in question. So in your example of Orion.Nodes I'd expand it and look at the bracketed names to find the NP I want. In your case, the most obvious one you want is: ActiveAlerts and not AlertActive:

However that doesn't include the field AlertObject, so your query still wouldn't work. 

That said, if you link to AlertObjects.AlertObjectID then your query works - if that is what you need then the query would be:

SELECT 
[nodes].Caption
, [nodes].NodeID
, [nodes].ObjectSubType
, [nodes].IPAddress
, [nodes].IPAddressType
, [nodes].DNS
, [nodes].Vendor
, [nodes].VendorIcon
, [nodes].Icon
, [nodes].Status
, [nodes].AlertObjects.AlertObjectID

FROM Orion.Nodes AS nodes

WHERE Caption LIKE'%srveucsc%'
ORDER BY Caption

Ah so i was going wrong because i wasn't selecting a NP from the orion.nodes table.

So i can't just use NP'S from another table in my query, only those above if there are any of use right?

Thanks again for all your help

bully79 said:

So i can't just use NP'S from another table in my query, only those above if there are any of use right?

To be 100% clear you can use NPs directly associated to the table your are looking at e.g. Orion.Nodes, and those indirectly linked through the directly linked NP's.

cool. Thanks

Also do you happen to know how there's a way of filling in blank gifs and just the file reference and the alertobject id's to mean something?

Status is fine

Thanks mate. much appreciated

That I don't know

Is this in a Modern Dashboard?  Can you share your original SWQL?  That'll help us understand the specifics.

Yes we are thanks. 

If i input the following, the table in the MD has just the .gif filenames and doesn't display them.

SELECT [nodes].Caption, 
[nodes].NodeID, 
[nodes].ObjectSubType, 
[nodes].IPAddress, 
[nodes].IPAddressType, 
[nodes].DNS, 
[nodes].Vendor, 
[nodes].VendorIcon, 
[nodes].Icon, 
[nodes].Status

FROM Orion.Nodes AS nodes
where Caption LIKE'%srveucsc%'
ORDER BY Caption

And it won't because that's not how it's designed to work with images.  That's why I wanted to get your original query to see if I can figure out a way to get this information to display in the way you'd like.

Ah ok. So you can't get gifs displayed in the table in modern dashes then?.

Thanks

Not in the way that it's done with the previous Custom Query widget.

What do you want this table to actually show?  Even insert a table and put in field titles and what you'd like to see in each field.