How to Query two tables?

Hi

As many tables are pre-joined you don't have to use "inner joins" when using SWQL (most times anyway).

So, you can use below as a base:

SELECT
    N.NodeID
    ,N.Caption
    ,N.CustomProperties.City
    ,N.VirtualMachine.DateCreated
FROM Orion.Nodes AS N

We start at the table "Orion.Nodes" and then jumps to both "customProperties-table" and "VirtualMachines-table" without joins as SolarWinds platform already know how they are connected.

So, just add the various customproperties-columns you have in your installation and you are done.

Great explanation, Seashore .  jruiz73 , these are called Navigation Properties  in the platform's API.

Great response, although quite often the navigation properties go both ways.   So if you'd rather, you could keep your original query and for each variable in the virtual machines you could refer to it by something like NCP.Node.VirtualMachine.DateCreated for one example.  Whichever makes more sense to you!

If you really want to do the JOIN, which you'd have to do if you were working in SQL vs SWQL, then you should choose a join field that makes more sense.  Joining on a date usually isn't a good idea because you don't know that the data in both tables was done at the same time, and its possible for you to match more data also - if multiple things had the same date in that field.   Most tables have a unique ID, that for related tables usually is consistent across those tables.   You don't have to include that field in your select to use it.  For most  Node oriented things it would be the "NodeID" field, which does exist on the NCP table.   However, since virtual machines usually aren't managed on their own, but managed under their virtual host, the box running VMWare, you might have to look for something different.   In this case I'm guessing the "HostID" is what you really want, although I'm just guessing, you'd have to test it.   So, join on NCP.NodeID = d.HostID?

Just wanted to flesh out your options.   If you can, stick with SWQL to make things easier, but always nice to know the differences with SQL!!

Thank you for your guidance. Is there such a thing as SWQL for beginners or etc?

Yep - the above link to Navigation Properties will plop you into a Wiki all about working with the SolarWinds Platform API.  Additionally, there are a few videos in the (Missing Group) Vault - just search in there for SWQL.