Active Diagnostics reported a bunch of duplicate IP addresses in the topology tracking. I wanted to find out which devices are showing layer 3 links to the same destinations and thought some of you would find the queries useful. I explicitly excluded 0.0.0.0 and 127.0.0.1 because I expect duplicates of these addresses for multiple routes outside and all the loopback interfaces.
This query finds node names that have duplicate next hop addresses:
SELECT Caption, NextHopAddress FROM NodesData
INNER JOIN NodeL3RoutingData ON NodesData.NodeID = NodeL3RoutingData.NodeID
WHERE NodeL3RoutingData.NextHopAddress IN (SELECT NextHopAddress
FROM NodeL3RoutingData
GROUP BY NextHopAddress
HAVING COUNT (*) > 1) AND NextHopAddress NOT IN ('0.0.0.0', '127.0.0.1')
And this query finds node names that have duplicate route destination addresses:
SELECT Caption, RouteDestination FROM NodesData
INNER JOIN NodeL3RoutingData ON NodesData.NodeID = NodeL3RoutingData.NodeID
WHERE NodeL3RoutingData.RouteDestination IN (SELECT RouteDestination
FROM NodeL3RoutingData
GROUP BY RouteDestination
HAVING COUNT (*) > 1) AND RouteDestination NOT IN ('0.0.0.0', '127.0.0.1')