Showing Duplicate IP Nodes in Orion DB

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

Danielle

It gave me an error...

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'is'.

What is your SQL server version?

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.

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

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.