cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Showing Duplicate IP Nodes in Orion DB

I wrote a filter that would show me all the nodes that happened to have the same IP address defined in the nodes table, excluding Dynamic IPs.

(select Dup1.NodeID from Nodes AS Dup1 INNER JOIN Nodes AS Dup2 ON Dup1.NodeID <> Dup2.NodeID AND Dup1.IP_Address = Dup2.IP_Address and Dup1.NodeID = Nodes.NodeID and Dup1.DynamicIP = 0 group by Dup1.NodeID) is not null

8 Replies
Level 12

Great Post! Especially since we have so many nodes with multiple IP addresses causing them to be added several times. But I don't understand your last two conditions in SQL. Would you mind explaining them?


Dup1.NodeID = Nodes.NodeID and Dup1.DynamicIP = 0 group by Dup1.NodeID) is not null

0 Kudos

The business intelligence engine places everything you type in the filter into the query's where clause. The 'Dup1.NodeID = Nodes.NodeID' is the key to tying this filter to the row returned by the engine's query. 'and Dup1.DynamicIP = 0' is to weed out potential matches because of dynamic IPs. 'group by Dup1.Node1' is necessary because when a dup is found, it's going to match against all the joins and appear multiple times.

I think that's the best way to explain it. Also... to weed out nodes that have been found with multiple IPs, use the caption based one. 

Level 14

It gave me an error...

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'is'.

0 Kudos

Oh, you're trying to run this in the management studio, this is a filter for the website. The SQL is...

select Dup1.*

from Nodes AS Dup1

INNER JOIN

Nodes AS Dup2

ON Dup1.NodeID <> Dup2.NodeID

AND (Dup1.IP_Address = Dup2.IP_Address)

and Dup1.DynamicIP = 0

order by Caption

That is finding them by IP, this one will do it by caption:

select Dup1.*

from Nodes AS Dup1

INNER JOIN

Nodes AS Dup2

ON Dup1.NodeID <> Dup2.NodeID

AND Dup1.Caption = Dup2.Caption

and Dup1.DynamicIP = 0

order by Caption

Thank you so much. I like them both. Found lots of dups.

0 Kudos

What is your SQL server version?

0 Kudos

SQL_Version.png

0 Kudos
Community Manager
Community Manager

Thanks for sharing!  You should upload this as a document in the Content Exchange too.

Danielle

0 Kudos