This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Way to join Bridge and ARP tables?? Need to build "where is this host?" type of reports

brian_duvall over 13 years ago

I am trying to write a query that will tell me all the devices on a particular network device as well as a report that will tell me where a host is on the network (Interface and network device its attached to).

I do nightly inventory grabs on all my network gear...I grab the ARP table as well as the bridge/mac table. We have a layer 3 core network with layer 2 access switches off these cores. The ARP table can give me the IP addresses from the core switches but not on the access switches and it shows the interface as the layer 3 vlan..not the actual interface its on.

The bridge/mac table gives me interface level information about mac addresses and where they are connected but I dont have IP info to match it up to.

I tried to do a SQL join on these two tables in order to get the info I need but the interface indexes don't seem to match up....so I don't see a way to do this type of report.

Can someone help me please!

Top Replies

chrisbidwell over 13 years ago +1

I wrote a query to do almost exactly this... SELECT N . SysName MACHolder , I . InterfaceName , I . InterfaceAlias , MF . MAC , A . IPAddress , …

0 chrisbidwell over 13 years ago

I wrote a query to do almost exactly this...
SELECT
      N.SysName MACHolder,
            I.InterfaceName,
            I.InterfaceAlias,
            MF.MAC,
            A.IPAddress,
            A.RDNSLookup,
            N1.SysName ARPHolder
FROM Nodes N
INNER JOIN Interfaces I on N.NodeID = I.NodeID
INNER JOIN BridgePorts BP on BP.NodeID = N.NodeID and BP.InterfaceIndex = I.InterfaceIndex
INNER JOIN MACForwarding MF on MF.NodeID = N.NodeID and MF.Port = BP.Port
LEFT JOIN ARPTables A on MF.MAC = A.MAC
INNER JOIN Nodes N1 on N1.NodeID = A.NodeID
WHERE N.SysName = 'YOUR DEVICE SYSNAME' and MF.Source = 'Learned' and A.Source != 'Static'
ORDER BY N.SysName, I.InterfaceIndex, MF.MAC
This will give you a list of all of the dynamic MAC Address Table entries based on the system name you specify, along with all of the dynamic ARP entries found on all other devices that reference that MAC address. Hope this helps you enough to get what you need.
Cancel
Vote Up +1 Vote Down

Cancel
0 brian_duvall over 13 years ago in reply to chrisbidwell

Thanks. I will test this out and see if it works.
Cancel
Vote Up 0 Vote Down

Cancel
0 Dasflipz over 13 years ago in reply to chrisbidwell

Oh man, this is exactly what I need, but when I run this query after replacing the SysName with the desired switch, I get nothing back.. I get the formatting, the columns and all that, but no actual data. Any thoughts?
Cancel
Vote Up 0 Vote Down

Cancel
0 Dasflipz over 13 years ago in reply to Dasflipz

Nevermind.. Turns out some of the tables are empty... so now I just need to figure out why that is.
Cancel
Vote Up 0 Vote Down

Cancel
0 Jason.Henson over 9 years ago

For the new integrated tables, here's the new query. I'm curious if somebody can define the difference between the result set under MACHolder alias and ARPHolder alias?
SELECT
N.SysName MACHolder,
I.InterfaceName,
I.InterfaceAlias,
MF.MAC,
A.IPAddress,
A.RDNSLookup,
N1.SysName ARPHolder,
MF.Source,
A.Source
FROM NCM_Nodes N
INNER JOIN NCM_Interfaces I on N.NodeID = I.NodeID
INNER JOIN NCM_BridgePorts BP on BP.NodeID = N.NodeID and BP.InterfaceIndex = I.InterfaceIndex
INNER JOIN NCM_MACForwarding MF on MF.NodeID = N.NodeID and MF.Port = BP.Port
LEFT JOIN NCM_ARPTables A on MF.MAC = A.MAC
INNER JOIN NCM_Nodes N1 on N1.NodeID = A.NodeID
WHERE N.SysName = 'YourDeviceSysname' and MF.Source = 'Learned' and A.Source != 'Static'
ORDER BY N.SysName, I.InterfaceIndex, MF.MAC
Cancel
Vote Up 0 Vote Down

Cancel