Keshav Shrivastava Keshav Shrivastava - 6 months ago 14
Bash Question

joining files by ip address

I have two files both have list of ip addresses, mac and descriptions in different columns. Some ip addresses are in both files. I want to join these files by ip addresses such that the output file has:
1) all ip addresses from both files and
2) Any IP which is listed in both files is followed by four columns 2 each from each file containing mac and description.

file1:

11.16.31.13 00:a0:c8:b5:c2:d5 keshav-ae1.0
10.16.31.17 f0:ad:4e:01:c5:c8 keshav-ge-2/1/5.0
108.16.31.3 4c:96:14:5d:5f:f0 keshav-ae0.0
108.16.31.4 00:0a:9c:52:74:b2 keshav-ae1.0
27.16.32.1 00:00:5e:00:01:4c keshav-ae0.0


file 2:

192.16.31.10 00:25:90:cd:4e:3c keshav-ae0.0
10.16.31.17 f0:ad:4e:01:c5:c8 keshav-ae0.0
17.16.31.2 b0:a8:6e:28:87:f0 keshav-ae0.0
108.16.31.4 00:0a:9c:52:74:b2 keshav-ae0.0
10.16.31.5 2c:36:f8:ce:65:42 keshav-ae0.0


Output file should be:

11.16.31.13 00:a0:c8:b5:c2:d5 keshav-ae1.0
10.16.31.17 f0:ad:4e:01:c5:c8 keshav-ge-2/1/5.0 f0:ad:4e:01:c5:c8 keshav-ae0.0
108.16.31.3 4c:96:14:5d:5f:f0 keshav-ae0.0
108.16.31.4 00:0a:9c:52:74:b2 keshav-ae1.0 00:0a:9c:52:74:b2 keshav-ae0.0
27.16.32.1 00:00:5e:00:01:4c keshav-ae0.0
192.16.31.10 00:25:90:cd:4e:3c keshav-ae0.0
17.16.31.2 b0:a8:6e:28:87:f0 keshav-ae0.0
10.16.31.5 2c:36:f8:ce:65:42 keshav-ae0.0


I have tried join with sorted files (both "sort -n" or "sort -n -t . -k 1,1 -k 2,2 -k 3,3 -k 4,4" ) but it's not giving desired output.

sort -n file1 > file3
sort -n file2 > file4
join -j 1 -a 1 -a 2 -e UNKNOWN file3 file4 > output


and output file looks like below:

10.16.31.17 f0:ad:4e:01:c5:c8 keshav-ge-2/1/5.0 f0:ad:4e:01:c5:c8 keshav-ae0.0
10.16.31.5 2c:36:f8:ce:65:42 keshav-ae0.0
11.16.31.13 00:a0:c8:b5:c2:d5 keshav-ae1.0
17.16.31.2 b0:a8:6e:28:87:f0 keshav-ae0.0
108.16.31.4 00:0a:9c:52:74:b2 keshav-ae0.0
192.16.31.10 00:25:90:cd:4e:3c keshav-ae0.0
27.16.32.1 00:00:5e:00:01:4c keshav-ae0.0
108.16.31.3 4c:96:14:5d:5f:f0 keshav-ae0.0
108.16.31.4 00:0a:9c:52:74:b2 keshav-ae1.0

Answer

If you want to keep the order of ip, you can try this.

awk 'ARGIND<3{dic[$1]=dic[$1]" "$2" "$3}ARGIND>=3 && !($1 in a){print $1dic[$1];a[$1]}' file1 file2 file1 file2

Output

11.16.31.13 00:a0:c8:b5:c2:d5 keshav-ae1.0
10.16.31.17 f0:ad:4e:01:c5:c8 keshav-ge-2/1/5.0 f0:ad:4e:01:c5:c8 keshav-ae0.0
108.16.31.3 4c:96:14:5d:5f:f0 keshav-ae0.0
108.16.31.4 00:0a:9c:52:74:b2 keshav-ae1.0 00:0a:9c:52:74:b2 keshav-ae0.0
27.16.32.1 00:00:5e:00:01:4c keshav-ae0.0
192.16.31.10 00:25:90:cd:4e:3c keshav-ae0.0
17.16.31.2 b0:a8:6e:28:87:f0 keshav-ae0.0
10.16.31.5 2c:36:f8:ce:65:42 keshav-ae0.0
Comments