SWQL query to get node-related IPAM network name

So we have a lot of networks in IPAM and I need to gather all NPM nodes, related to certain subnets.
I made a draft, but since Solarwinds doesn't support subqueries with more than 1 returned value, it is very ugly:

  [Subnet].FriendlyName AS [SUB]
, Nodes.Caption
, Nodes.DNS
, Nodes.IP
, Nodes.CustomProperties.City
, 'https://domain'+Nodes.DetailsUrl AS [URL]
, Nodes.Status
, Nodes.CustomProperties.NodeRole

FROM Orion.Nodes AS [Nodes]
  ON [Nodes].ip = [IP].IPAddress
LEFT JOIN IPAM.GroupReport AS [Subnet]
  ON [Subnet].GroupId = [IP].SubnetID

--WHERE [Subnet].FriendlyName = 'ipam_network_name'

[Subnet].FriendlyName = 'ipam_network_name1' OR
[Subnet].FriendlyName = 'ipam_network_name2' OR
[Subnet].FriendlyName = 'ipam_network_name3' OR
~100 strings like that
[Subnet].FriendlyName = 'ipam_network_name104' OR
[Subnet].FriendlyName = 'ipam_network_name105' OR
[Subnet].FriendlyName = 'ipam_network_name106'
Could you tell me please how to optimise this?
As a result I just wanna have an additional column with IPAM Network name near every node.
  • what you have looks like it should be close to working, are you just hoping not to list all 100+ networks? Is there a way to filter on the name? Can you do like 'Ipam_network_name%' or something similar? We prefix our names for various uses. Below is just another way to do it that is a bit shorter? 

    [Subnet].FriendlyName in (
    , 'ipam_network_name2'
    , 'ipam_network_name3'
    ~100 strings like that
    , 'ipam_network_name104'
    , 'ipam_network_name105'
    , 'ipam_network_name106'
    Or can you put custom properties on the IP or IP Group Node for filtering? 
  • SWQL doesn't have many difference in how it handles subqueries than normal SQL.  What do you think would be valid SQL that you are trying to work around with your excessively verbose where condition.  The only place you can't have multiple values is in the select area which is also true in plain SQL.

  • This Source code is too great I'm also a programmer recently. My Favorite programming language is Ruby.