Report showing only ports with more than 2 MACs

sachsj

Can someone please help me figure out how to only report on those interfaces that have more than two MACs?

SELECT  TOP 10000 UDT_AllEndPoints.ConnectedTo AS ConnectedTo,
UDT_AllEndPoints.PortNumber AS PortNumber,
UDT_AllEndPoints.IPAddress AS IPAddress,
UDT_AllEndPoints.MACAddress AS MACAddress,
UDT_AllEndPoints.Hostname AS Hostname,
UDT_AllEndPoints.VLAN AS VLAN,
Nodes.NodeID AS NodeID,
UDT_AllEndPoints.ConnectionTypeName AS ConnectionTypeName,
UDT_AllEndPoints.PortID AS PortID,
UDT_AllEndPoints.ConnectionType AS ConnectionType

FROM
UDT_AllEndPoints INNER JOIN Nodes ON (UDT_AllEndPoints.NodeID = Nodes.NodeID)

WHERE 
(
  (UDT_AllEndPoints.VLAN = 100) AND
  (UDT_AllEndPoints.PortNumber LIKE '%Gi%')
)

michalB

This one might work for you:

select * from UDT_AllEndPoints

where PortID in (select PortID from UDT_PortToEndpointCounts where EndpointCount > 2)