Creating SWQL joins using the links in SWQL studio

Hi guys,

I am currently stuck creating joins using the link icons in SWQL studio. See below example:

I know that some how i need to use 'As' and create and alias in that query and reference it. In the above query i have info from orion.nodes in the query but i want to add info from orion.alertobjects table and 'alertactive' link in the same query and add it to the above.

As mentioned i guess it's by using an alias?.

Thanks for your help

  • 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 Slight smile

  • 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

  • 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