SWQL query to get node-related IPAM network name

omavel

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:

SELECT
  [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]
JOIN IPAM.IPNodeReport AS [IP]
  ON [Nodes].ip = [IP].IPAddress
LEFT JOIN IPAM.GroupReport AS [Subnet]
  ON [Subnet].GroupId = [IP].SubnetID

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

WHERE 
[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.

jm_sysadmin

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_name1'
, '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?