[SOLVED] Compare two files for common strings NOT line-by-line

rklingsten · 2015-11-04 19:30:54

Howdy, I need to compare two files and output the line in file 2 which matches a string from file 1. As you will see I am trying to get a MAC to IP mapping from the MAC and ARP tables on Cisco switches/routers. Thanks for any suggestions!

file 1:

30,0040.9d58.5879,dynamic,ip,ipx,assigned,other,GigabitEthernet7/22,
130,0007.4d22.992b,dynamic,ip,ipx,assigned,other,GigabitEthernet7/34,
130,0007.4d25.5b80,dynamic,ip,ipx,assigned,other,GigabitEthernet7/33,
130,0007.4d30.31aa,dynamic,ip,ipx,assigned,other,GigabitEthernet7/31,

file 2:

Internet  10.21.198.231           0   6c3b.e53a.a109  ARPA   Vlan130
Internet  10.20.83.115          128   0007.4d22.992b  ARPA   Vlan130
Internet  10.20.85.117            0   bc30.5b9e.733e  ARPA   Vlan130
Internet  10.20.80.113            0   c434.6b71.8e79  ARPA   Vlan130

output:

Internet  10.20.83.115          128   0007.4d22.992b  ARPA   Vlan130

Last edited by rklingsten (2015-11-04 20:04:39)

jasonwryan · 2015-11-04 19:41:28

Using Awk (tricky and fragile because of the different field separators):

awk -F'[, ]' 'FNR==NR{a[$2];next};($16 in a)' file1 file2
Internet  10.20.83.115          128   0007.4d22.992b  ARPA   Vlan130

rklingsten · 2015-11-04 19:49:50

Thanks so much! It's close, but not working, dumping all of file 2 ...

I did try your code on the example lines I posted and it works there. Can you explain a little bit how the code works, maybe I can figure out where it's busting... I have never used awk.

rklingsten · 2015-11-04 20:03:50

Aha got it! Your script was excellent in pointing the way. I processed the one file more to get rid of competing seperators in favor of comma. Then I did this:

awk -F',' 'NR==FNR{a[$2];next}$4 in a{print $2}' file1 file2

Thanks again!

ewaller · 2015-11-04 21:22:22

Grrr.... I just realized you edited the first post -- That is why I could not figure our why Jason was using $16. The input files changed format!

Let's see if my awk is improving....

So if NR equals FNR you are reading the first file and have not yet found the second file. If you are in the first file, add the second parameter (the MAC) to an an associative array as an index with no assigned value, then skip to process the next record. If you are not in the first file the record is from the second file, so pick the MAC based upon its known position in the records of the second file (field 4). See if that MAC is in the associative array, and if it is spew forth the IP address from the current record field 2.

Jason, is reading a file in in that manner a common construct in awk, or are you just a frigin awk wizard?

Last edited by ewaller (2015-11-04 23:09:53)

manchoz · 2015-11-04 23:00:45

Hi,
this is an alternative one-liner solution using just coreutils and some unusual Bash feature.

join -1 2 -2 4 <(<file1 tr ',' ' ' | sort -k2) <(<file2 sort -k4) | cut -d' ' -f 10-

Let me know if it works for you :-)

jasonwryan · 2015-11-04 23:01:42

Yes it is one of the standard Awk idioms (sadly, I cannot claim any wizardry).

Nicely interpreted, ewaller: that saved me quite some typing

bulletmark · 2015-11-05 05:15:55

I just do these kind of things the simple way:

#!/bin/bash
while read line; do
    set -- $line
    if grep -q $4 file1; then
        echo $line
    fi
done <file2

Arch Linux

#1 2015-11-04 19:30:54

[SOLVED] Compare two files for common strings NOT line-by-line

#2 2015-11-04 19:41:28

Re: [SOLVED] Compare two files for common strings NOT line-by-line

#3 2015-11-04 19:49:50

Re: [SOLVED] Compare two files for common strings NOT line-by-line

#4 2015-11-04 20:03:50

Re: [SOLVED] Compare two files for common strings NOT line-by-line

#5 2015-11-04 21:22:22

Re: [SOLVED] Compare two files for common strings NOT line-by-line

#6 2015-11-04 23:00:45

Re: [SOLVED] Compare two files for common strings NOT line-by-line

#7 2015-11-04 23:01:42

Re: [SOLVED] Compare two files for common strings NOT line-by-line

#8 2015-11-05 05:15:55

Re: [SOLVED] Compare two files for common strings NOT line-by-line

Board footer