This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL query to get node-related IPAM network name

omavel over 3 years 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

0 jm_sysadmin over 3 years ago

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?
Cancel
Vote Up +2 Vote Down

Cancel
0 mesverrum over 3 years 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
Vote Up +2 Vote Down

Cancel
0 jamesanderson01 over 2 years ago

This Source code is too great I'm also a programmer recently. My Favorite programming language is Ruby.
Cancel
Vote Up 0 Vote Down

Cancel