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

UDT - Multiple MAC addresses per port, limited by XXX

I modified the default UDT Connected MAC and IP Addresses report to only pull ports that have more than one MAC address connected.

SELECT Nodes.Caption,
UDT_ConnectedMACsAndIPs.PortNumber AS PortNumber,
UDT_ConnectedMACsAndIPs.PortName AS PortName,
UDT_ConnectedMACsAndIPs.ConnectedMACs AS ConnectedMACs,
UDT_ConnectedMACsAndIPs.ConnectedIPs AS ConnectedIPs,
UDT_ConnectedMACsAndIPs.PortID AS PortID
FROM UDT_ConnectedMACsAndIPs
INNER JOIN Nodes
ON (UDT_ConnectedMACsAndIPs.NodeID = Nodes.NodeID)
WHERE ConnectedMACs > 1

I would like to limit these results to non-infrastructure ports, i.e. user access ports that have no business hosting multiple MAC addresses. I'm curious if anyone with more intimate knowledge of UDT's database might know of a property that I could use to limit the search.

I've considered a limit by interface description, but that would require us to trust descriptions and keep them accurate. I'm willing to do that, but I was trying to come up with a way that was a little more concrete, as we've had issues with folks who "forget" to update descriptions in the past.

I thought that if I could somehow identify a CDP relationship within UDT, I could have the report ignore any ports that have a CDP neighbor. This would require us to police CDP on user ports, but that is good practice regardless and should be implemented.

We eventually plan to move to VoIP, which will introduce new complications with the report. We'll likely just limit it to more than two MACs and call it good. I don't believe they will ever be Cisco IP phones, so the CDP possibility there doesn't worry me. Any IP phones on the network would probably use LLDP.

Thoughts?

0 Kudos
3 Replies
MVP
MVP

Can you put a template out here to import into report writer. Thanks.

Level 18

Our dev team suggests using the following query:

SELECT Nodes.Caption, 

UDT_ConnectedMACsAndIPs.PortID AS PortID,

UDT_ConnectedMACsAndIPs.PortNumber AS PortNumber, 

UDT_ConnectedMACsAndIPs.PortName AS PortName, 

UDT_ConnectedMACsAndIPs.ConnectedMACs AS ConnectedMACs, 

UDT_ConnectedMACsAndIPs.ConnectedIPs AS ConnectedIPs, 

UDT_ConnectedMACsAndIPs.PortID AS PortID,

FROM UDT_ConnectedMACsAndIPs  

INNER JOIN Nodes

ON (UDT_ConnectedMACsAndIPs.NodeID = Nodes.NodeID) 

WHERE ConnectedMACs >

  AND PortID NOT IN (SELECT Port1ID FROM UDT_PortToPortCurrent)

Regards,

Jiri

That seems to have done the trick. Thanks, Jiri!