Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Getting multiple IP address results for 1 node on a single interface index.

I am trying to pull all my interfaces - from specific devices (Fortigate)
but when I start combining table

Node → Interfaces → NodeIPAddresses to get:

VLAN ID
IP
SUBNETMASK
INTERFACE CAPTION

My work in progres query:

SELECT n.NodeID AS ID, n.Caption AS Device_Name, n.IP_Address AS Node_Management_IP, i.Caption AS Interface_Caption, ipa.IPAddress AS Interface_IP, ipa.SubnetMask AS Mask

FROM Orion.NPM.Interfaces i

INNER JOIN Orion.Nodes n ON n.NodeID = i.NodeID
LEFT JOIN Orion.NodeIPAddresses ipa ON ipa.NodeID = n.NodeID AND ipa.InterfaceIndex = i.InterfaceIndex

WHERE i.Caption IN ('VLAN1', 'VLAN2', 'VLAN3') AND i.Description LIKE '%l2vlan%' AND ipa.IPAddress IS NOT NULL AND ipa.IPAddressType = 'IPv4' AND i.Status = '1'ORDER BY n.Caption

For example on 1 node 860 with interface index 20
I get 2 IP's, one from the past and one from today

I cant figure out to only show the current/latest on. There is not date creation, or change field in the table

How do I get only my current IP?

example:

Find more posts tagged with

report

Accepted answers

All comments

Lofstrand

Those tables should only show current values. Can it be that the interface actually have two addresses?

Check with

SELECT TOP 1000 NodeID, IPAddress, IPAddressN, IPAddressType, InterfaceIndex, SubnetMask
FROM Orion.NodeIPAddresses
where IPAddressType='IPv4' and nodeid=860

blacet

Ok I have checked, on GUI it has only 1 IP and on the device itself when I run show | grep on full config, I only get 1 result:

my test/example, on SWQL I see InterfaceIndex 20 - with 2 IP's :
172.29.81.254
10.60.37.254

When I search for them on the full config it shows me this:

show | grep 172.29.81.254
no results

show | grep 10.60.37.254
set ip 10.60.37.254 255.255.255.0

So only one is actually used on my device/interface, for me it looks like the other is a historical value or something?

blacet

On GUI in SolarWinds it't the correct one.

cnorborg

Sorry, had to modify your query to fit my environment, having no Fortigates (thank heaven!!) Pretty sure you can make it workable for you once again.

First, I'd say try and simplify things. All 3 of the tables your working with have "Navigation Properties", or what I like to call implicit joins. Just look for the chain link symbol in SWQL Studio and you'll see them. The Nodes table is accessible from the Interfaces table using the "Node" navigation property. The NodeIPAddresses is accessible from the Interfaces table using the "IPAddress" Navigation property. So, using those there are now zero joins.

But, the other thing I saw that was maybe flawed is the NodeIPAddresses table is a many-to-one relationship with the Nodes or Interfaces table. You have to constrain it to match interface to interface. The way to do this is using the "InterfaceIndex" in each table. I also noticed I was still getting duplicates, but all of them (maybe, check this) had one with a subnet mask, and the others subnet mask was null, so I'm excluding the null subnet masks. I'm assuming maybe one entry is for the /32 local interface, and one for the actual IP? ie: go look in a routing table at the IP's associated with connected routes? This is just a guess.

So, taking your query, getting rid of the joins by using Navigation Properties, and putting in a constraint to match interface indexes (i.InterfaceIndex = i.IPAddress.InterfaceIndex), I ended up with this.

SELECT TOP 1000 i.Node.NodeID AS ID, i.Node.Caption AS Device_Name, i.Node.IP_Address AS Node_Management_IP, i.Caption AS Interface_Caption, i.IPAddress.IPAddress AS Interface_IP, i.IPAddress.SubnetMask AS Mask

FROM Orion.NPM.Interfaces i

WHERE (i.IPAddress.IPAddress IS NOT NULL) AND (i.Status = '1') AND (i.IPAddress.IPAddressType = 'IPv4') AND (i.InterfaceIndex = i.IPAddress.InterfaceIndex) AND (i.IPAddress.SubnetMask IS NOT NULL)
ORDER BY i.Node.Caption

blacet

Thanks, I have ran the query and indeed now I have 0 joins. Thanks for explaining me about the naviagaion links, usefull tip, much appreciated.

The thing is, putting this Subnetmask filter in the where clause:
i.IPAddress.SubnetMask IS NOT NULL

I only get 8 results, and these rusults show indeed the correct IP.
But, I am expecting arround 100+ results. Most of them have Mask NULL.

Lofstrand

That sounds a bit strange.
I did a test here, added another IP address on an Interface. Did a rediscover in Solarwinds and the new IP address was there. Then I removed the address from the device and did a new rediscover and the IP address was removed from the device and the NodeIPAddresses table. Right away.

If you press "rediscover" manually on a node, does it remove the address? (give it a few minutes to complete the task)

cnorborg

As I noted, you'll have to adjust to your environment. Perhaps you need the ones with mask null instead of without? Or maybe you need both?

blacet

I did the same test.

It will show after adding it on an interface (loopback for testing), but it is not removed from the table after removing it from the interface. The "All IP Addresses widget" and NodeIPAddresses table only grows.

By the way adding it, and rediscover took 1 sec to show the new IP.

blacet

It will show me the double entries again, the issue is what @Lofstrand demonstrated.

My IpAddress table only grows, old IP's that no longer live on my device config will still be visible in the table Orion.NodeIPAddresses

For my test I added an extra IP on the loopback, and rediscover the device. It apeard. Then I remove the IP and rediscoverd, still the IP exist on the IP address widget + its visible in my query on the Orion.NodeIPAddresses table. But its gone from the device config.

blacet

Found this old thread showing the same behaviour in 2016

All Ip Adresses field showing legacy Ip Address - THWACK

cnorborg

Oh wow, another discussion I was involved in, and from 10 years ago almost! Yea, I wouldn't advise to go cleaning the database yourself. Most likely, the actual issue is you need to look at other fields that you aren't yet looking at, to try and find something that differentiates between the duplicates. ie: in my environment I though that was the mask being NULL. But in your environment it might be something different. Note: it could even be a related field in another database table. If that is the case, it will most likely be something in a table with a Navigation Property - most likely…

blacet

I have come up with a dirty solution, I just count the ipaddresses and then drop the "old" ones. Its not "the" solution but it will work for now.