SWQL for Site to site VPN connection widget

For the one NodeID, you can connect using Navigation Properties.  My SWQL below is connecting the Remote Side's IP with the IP from Orion.Nodes and then using that to retrieve the Caption.

SELECT [VPN].Node.Caption
	,[VPN].SourceIPAddress
	,[VPN].SourceHostname
	,[VPN].TargetIPAddress
	,[VPN].TargetHostname
	,[Remote].Caption
	,[VPN].StartTime
	,[VPN].Status
	,[VPN].StatusDescription
FROM Orion.VPN.L2LTunnel AS VPN
LEFT JOIN Orion.Nodes AS [Remote]
  ON [VPN].TargetIPAddress = [Remote].IPAddress

I should note that I do not have any VPN tunnels in my lab, so I can't validate the correctness of the query, but it does execute without errors, so that's something.

Thanks for the reply. That's the part I've got working at the moment. I can display a table of VPN connections and use mw SWQL statement to get the name of which local firewall is hosting the VPN connections from Orion.Nodes.

What I would now like to do is get the host name of the remote IP address from Orion.Nodes. So I'm basically wanting to get 2 values from Orion.Nodes in the SWQL statement.

Any chance you can paste in the results of my above query(or attach as a CSV)?

That worked, just what I'm looking for, it includes the name of the remote site as I wanted!! I would appreciate an explanation of how that works, particularly the [VPN].Node.Caption as I can't understand how that path is found in the database.

Thanks a lot KMSigma, I'm going to work on the red/green lights now.

See the things with the "Chain link" icon?  Those are called "Navigation Properties."  I touch on them very briefly in SolarWinds Lab #91.

Long story short, they do the SQL "joining" without you needing to the join yourself yourself.  If you are using SWQL Studio, the autocomplete functionality recognized these links.

Above I have the entire entity Orion.VPN.L2LTunnel aliased to VPN.

Then when I ask for [VPN].Node.<FieldName>, the API knows that I want to get information from the Orion.Nodes entity for the matching NodeID from Orion.VPN.L2LTunnel.

Got you Thanks again, I really appreciate your help.