Correlating ARP to CAM tables (interface-to-IP)

I would appreciate a solution to my following problem:

I have a relatively simple network:  One cisco distribution switch (full layer-3), connected to two acess switches  (layer-2 only).

For a particular vlan, I have grabbed the output from the cam tables for both access switches:  So now I have a pretty table showing MAC address and the corresponding interface.

On the distro switch, is grabbed the output from the arp table for the same vlan, giving me another pretty table showing the MAC address and the IP address.

How do I go about correlating the two?  It's too large for me to do manually.  Both tables have a common element:  The MAC address.  I want to be able to spit out a spreadsheet (or similar) combining all three elements (IP address, MAC address, Interface).

I've tried using bash scripting (which I'm weak on) as well in MS-Excel (which I'm also pretty weak on).  Any suggestions?

This is rather urgent so max point guaranteed for first working response!
LVL 5
GenexenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jburgaardCommented:
If mac-adres realy are the same
put the 2 tabels in the same spreadsh.  side by side.
mark IP vs MAC, sort by MAC
mark MAC vs Interface, sort by MAC
GenexenAuthor Commented:
hi jburqaard,

That *would* work, provided that I had a neat 1-for-1 ip-per-port network.

However, many of the hosts connected to the access swtiches have teamed NICs, so I'll see their virtual ip/mac on both access switches (one on an access port, the other on a trunk port).  I need to get these multiple entries accounted for.  I realize each IP vs. MAC will be unique, however there may be multiple MAC vs. Interface for units with redundant uplinks.
jburgaardCommented:
Eventualy you can check for minor dif.'s in mac-cells by means of a col. with  formulas something like =a1=b1 (TRUE if a1 and b1 are equal).
If you delete partial row without match, let the rest pop up and you wil end up with all 'TRUE'

If the tables are real big, consider database.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

lrmooreCommented:
Check out SolarWinds switchport mapper. Download a free 30-day eval
http://www.solarwinds.net/Tools/Network_Discovery/Switch_Port_Mapper/index.htm
GenexenAuthor Commented:
thanks for that link lrmoore, that application looks like a winner.  My only concern is the pricetag given that I'll need to support this type of report beyond 30 days.

I'd still like to look at a method of correlating the tables I already have.  I know it can be done, I simply lack the syntax to make it work.
lrmooreCommented:
Then check out Kiwi Cattools. It's free
http://www.kiwisyslog.com/cattools2.htm

GenexenAuthor Commented:
lrmoore:  are you sure Cattools will work in this situation?  The problem I've run into in the past with Kiwi is that my L3 translations (MAC-IP) and L2 translations (MAC-Int) are on different boxes, therefore the "Report.Master.PORT-MAC-ARP.x-ref" doesn't give me any output.
jburgaardCommented:
If you still have the problem, there is a query-wiz. in Access.
If you alredy have tabels in Excel:

Make a new empty db.
From file menu Import external data of excel-file type.
repeat for mac-ip and mac-port.

From function menu choose relations:
Add mac-ip table
Add mac-port table
Make a one to many relation based on mac-mac.Save.

Run Query guide.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GenexenAuthor Commented:
Good answers all.  After much cursing at Excel, I downloaded Cygwin to my desktop and did the following:

1.  Created a new file consisting of only the mac addresses from the ARP table on my distribution switch (mac.txt).
2.  Kept my existing files of IP-to-MAC (macip.txt) and MAC-to-Interface (macint.txt)

3.  I ran a 'for i in' loop:

$ for i in `cat mac.txt`; do grep $i macint.txt; grep $i macip.txt; done > IP-MAC-INT.txt

and finished with a nice table that I could re-import back into excel and clean up & make pretty.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Apple Networking

From novice to tech pro — start learning today.