Q:

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

A:

If you want to keep the order of your excepted output, 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

Explanation
ARGIND<3 means the following statement {dic[$1]=dic[$1]" "$2" "$3} will only process the first 2 input files, and the statement {dic[$1]=dic[$1]" "$2" "$3} just put the data into dictionary(in Python) or map(in C++/Java etc.) dic by using ip as key and the mac with descriptions as values.
ARGIND>=3 meas the following statement {print $1dic[$1];a[$1]} will handle other files exclude the first 2 files. !($1 in a) means the key not in dictionary a, so that the output will not duplicate. The statement {print $1dic[$1];a[$1]} just print the ip, mac, description and update the ip to dictionary a.
The third and fourth param here is just to scan file for key so that the result can output as what you want.

If you only want to join the table, and don't care the order of output, you can just use
awk '{dic[$1]=dic[$1]" "$2" "$3}END{for(item in dic) print item""dic[item]}' file1 file2
Output
17.16.31.2 b0:a8:6e:28:87:f0 keshav-ae0.0
192.16.31.10 00:25:90:cd:4e:3c 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
10.16.31.5 2c:36:f8:ce:65:42 keshav-ae0.0
27.16.32.1 00:00:5e:00:01:4c keshav-ae0.0
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

Explanation
The statement {dic[$1]=dic[$1]" "$2" "$3} do the same thing (put the date into the dictionary dic) and the statement END{for(item in dic) print item""dic[item]} means that after every input files being processed, for each key item in the dictionary, do print key and value.

