SWQL query to get node-related IPAM network name

omavel 19 days ago

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.

Top Replies

Parents

0 mesverrum 19 days ago

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.
Cancel
Up +1 Down

Reply

Verify Answer

Cancel

Reply

0 mesverrum 19 days ago

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.
Cancel
Up +1 Down

Reply

Verify Answer

Cancel

Children

No Data