[NTLUG:Discuss] Is there an easy way?

[Patrick R. Michaud]

On Sun, Jul 26, 2009 at 12:06:14AM -0500, Leroy Tennison wrote:
> I'm not even sure how to Google for this one without having to wade 
> through too many extraneous hits so I'm posting here.  I have three 
> files and need to merge fields from each of them into a single file.  A 
> brief description of each is:
> 
> File 1 has a phone number and an associated IP address (VOIP, Avaya PBX)
> File 2 has an IP address and an associated MAC address (Cisco router).
> File 3 has a MAC address and associated Cisco switch port assignment.
> 
> I need a file containing phone number and associated switch port.
> [...]  Wondering if there's a utility to take 
> the files directly and do the "merge".  

You might want to look at the "join" command then  ("man join").  
Here's an example:

    $ cat file1   # phone to IP, sorted by IP
    555-1212    192.168.1.1
    555-1458    192.168.1.2
    555-0345    192.168.1.3
    555-1323    192.168.1.4
    555-2143    192.168.1.5
    $ cat file2   # IP to MAC, sorted by IP
    192.168.1.1    00:12:3f:24:07:65
    192.168.1.2    00:12:3f:14:15:16
    192.168.1.3    00:12:3f:01:01:01
    192.168.1.4    00:12:3f:78:78:78
    192.168.1.5    00:12:3f:54:12:36
    $ cat file3   # MAC to switch port, sorted by MAC
    00:12:3f:01:01:01   port23
    00:12:3f:14:15:16   port05
    00:12:3f:24:07:65   port16
    00:12:3f:54:12:36   port01
    00:12:3f:78:78:78   port08
    $ join -1 2 file1 file2 | sort -k 3 | join -1 3 - file3
    00:12:3f:01:01:01 192.168.1.3 555-0345 port23
    00:12:3f:14:15:16 192.168.1.2 555-1458 port05
    00:12:3f:24:07:65 192.168.1.1 555-1212 port16
    00:12:3f:54:12:36 192.168.1.5 555-2143 port01
    00:12:3f:78:78:78 192.168.1.4 555-1323 port08

Note that "join" expects each input file to be sorted on the field
to be joined (easily done using "sort").  Here then, is what that
final command is doing:

  * "join -1 2 file1 file2"  produces a file by matching the IP
    addresses in file1 and file2, resulting in a mapping of
    phone numbers to MAC addresses.  The "-1 2" option says that
    we want to join based on the second column of file1 (the
    first column of file2 is assumed).

  * "sort -k 3"  sorts that output by MAC address, so we can join
    it with file3 in the next step

  * "join -1 3 - file3"  matches the MAC addresses coming from the
    previous join+sort with the MAC addresses in file3.  The "-1 3"
    option tells join that the join field from the standard input
    is the third column (again, the first column of file3 is assumed).

Hope this helps!

Pm