Compare two files and merge columns in a third

CM64 · April 4, 2007, 10:25am

Hi,

I'm working with snmp, with a little script I'm able to obtain from a switch a list with a couple of values with this format

Port Mac
1 00:0A:0B:0C:0D:0E
....
(hundred of entries)

Now with a simple arp on a router I am able to obtain another list

00:0A:0B:0C:0D:0E pcname.mydomain.tld
00:0A:0B:1C:0D:0E 1.2.3.4
....
(hundred of entries, it could be a FQDN or an IP number cause not all the entries are known in DNS server)

The problem is to have a little batch script that takes the first file reads the first entry compares the MAC with all the entries in the second file and if it find a match writes the output in a third file, something like

1 00:0A:0B:0C:0D:0E pcname.mydomain.tld

if it doesn't find a match write the same the row maybe adding something like not found.

An this until all the entries in the first file are searched out.

Someone can help?

vgersh99 · April 4, 2007, 10:39am

nawk '
  FNR==NR { arp[$1]=$2; next }

  { printf("%s%s%s\n", $0, OFS, ($2 in arp) ? arp[$2] : "not found") }

' FILEarp.txt FILEsnmp.txt

radoulov · April 4, 2007, 10:42am

Post deleted - misread the question.

CM64 · April 4, 2007, 11:30am

Thank you, very fast and useful replay, it works.

Another question, my first script (or better Ivanov script see http://groups.google.it/group/comp.dcom.sys.cisco/browse_thread/thread/80d7928cd742f5db/33f5ef649d853b7c?lnk=st&q=OID\+SNMP\+superstack&rnum=2\#33f5ef649d853b7c\) has this structure

#!/bin/sh
#
#
Mac=".1.3.6.1.2.1.17.4.3.1.1"; Port=".1.3.6.1.2.1.17.4.3.1.2"
#
Script=`basename $0`
Host=.........
Pass=........ # SNMP community name
#
GET () { snmpwalk -Onq -c $Pass -v 1$Host| $1 sed -e s/^$1\.// -e s/\"//g |
sort ;}
JOIN () { join -o 1.2,2.2,2.3,2.4,2.5,2.6,2.7 $1 $2 | sort ;}
#
for OID in Mac Port
do eval "GET \$$OID > /tmp/${Script}\$$OID" ; done

JOIN /tmp/${Script}${Port} /tmp/${Script}${Mac} | sort -nk 1,2 >> snmp.txt

How can I substitute the variable Host with a pointer to a text file with a series of host, one per line, and produce for each one of them a file maybe in /tmp in the format IPOFSWITCH.txt?

Shell_Life · April 4, 2007, 11:39am

Here is one possible solution for your original post using only unix commands:

### Create matched records: in update file and also in the master file.
egrep -f upd_file master_file > matched_file

### Create not matched records: in update file and not in matched file.
egrep -v -f matched_file upd_file > tmp_not_matched_file

### Add "NOT FOUND" to the end of not matched records.
sed 's/$/NOT FOUND/' tmp_not_matched_file > not_matched_file

### Merge and sort all records into solution file.
sort matched_file not_matched_file > solution_file

CM64 · April 4, 2007, 11:53am

I'm testing the egrep solution but I have no match. Maybe I do some errors.

This is the egrep command

egrep -f upd_file master_file > matched_file

I suppose upd_file is the one with ip port and mac, master the one with mac and name or ip.

I've tried to invert the files, for first the one with mac and name or ip but no luck, what I'm doing wrong?

anbu23 · April 4, 2007, 11:59am

Shell_Lifes solution wont work

Shell_Life · April 4, 2007, 12:02pm

ANBU, can you explain why my solution won't work?

vgersh99 · April 4, 2007, 12:05pm

cm64:

Thank you, very fast and useful replay, it works.

Another question, my first script (or better Ivanov script see http://groups.google.it/group/comp.dcom.sys.cisco/browse_thread/thread/80d7928cd742f5db/33f5ef649d853b7c?lnk=st&q=OID\+SNMP\+superstack&rnum=2\#33f5ef649d853b7c\) has this structure

#!/bin/sh
#
#
Mac=".1.3.6.1.2.1.17.4.3.1.1"; Port=".1.3.6.1.2.1.17.4.3.1.2"
#
Script=`basename $0`
Host=.........
Pass=........ # SNMP community name
#
GET () { snmpwalk -Onq -c $Pass -v 1$Host| $1 sed -e s/^$1\.// -e s/\"//g |
sort ;}
JOIN () { join -o 1.2,2.2,2.3,2.4,2.5,2.6,2.7 $1 $2 | sort ;}
#
for OID in Mac Port
do eval "GET \$$OID > /tmp/${Script}\$$OID" ; done

JOIN /tmp/${Script}${Port} /tmp/${Script}${Mac} | sort -nk 1,2 >> snmp.txt

How can I substitute the variable Host with a pointer to a text file with a series of host, one per line, and produce for each one of them a file maybe in /tmp in the format IPOFSWITCH.txt?

Firstly, you didn't quote the original correctly - the way you quoted it would not have worked anyway.

Here's my attempt at correcting it - not tested:

      HostFile='pathTOhostFile'
      Pass=........    # SNMP community name
#
   GET () { 
     while read Host
     do
         snmpwalk -Onq $Host $Pass $1 | sed -e s/^$1\.// -e s/\"//g | sort
     done < "${HostFile}"
   }

Shell_Life · April 4, 2007, 12:05pm

CM, "upd_file" is the file containing:
Port Mac
1 00:0A:0B:0C:0D:0E
....
(hundred of entries)

"master_file" is the file containing:
00:0A:0B:0C:0D:0E pcname.mydomain.tld
00:0A:0B:1C:0D:0E 1.2.3.4

CM64 · April 4, 2007, 12:08pm

I've done a man egrep and indeed egrep -f canno't work in our case.

Everyway the awk solution works well. I've got the host issue to solve and my little script is ready. Of course I can make one script for every switch and then call them all in another one but is not "fairy". Someone can help this very beginning shell scripter?

anbu23 · April 4, 2007, 12:13pm

$ cat file1
1 00:0A:0B:0C:0D:0E
$ cat file2
00:0A:0B:0C:0D:0E pcname.mydomain.tld
00:0A:0B:1C:0D:0E 1.2.3.4

$ egrep -f file1 file2
$

is similar to

$ egrep "1 00:0A:0B:0C:0D:0E" file2
$

Here file1 is the file which contains the pattern list which contains port followed by mac. In file2 you have mac followed by ip. In the searching file file2 you dont have port before mac

CM64 · April 4, 2007, 12:16pm

No, I am sure it works but sure, I've done some changes in the original because that one doesn't work. There was an error in snmpwalk syntax (for my switch of course, for others maybe it works well), I had to specify -c and -v flag (not present in the original), some OIDS was unuseful to obtain ip/mac pair and for last I had to modify JOIN in a simpler form.

Thanks for the host construct, I'm going to try it in minutes.

vgersh99 · April 4, 2007, 12:23pm

probably your community strings and the SNMP versions will be different based on the 'Host'.

You may want to do a 'Get' on the 'sysDescr' and depending on the output use different OIDs for different 'hosts' - just an idea.

CM64 · April 4, 2007, 12:50pm

It's a good one, but I do not want to add to much complexity for the moment (now the script is very simple and I alredy need a lot of help ...). By the way, the host trick gives me some troubles. I've got some output with mac and port bat at console I see:

Unknown host (.1.3.6.1.2.1.17.4.3.1.1) (Connection timed out)

It seems that some of the parameters are going in the wrong place ... and there is the question of the output, I obtain a unique file and mac and port entries are all mixed so I have more ports with same number of different switches but there is no way to understand which one switch. I can solve this only in two ways, to add the switch ip string in each row something like

switch port mac
1.2.3.4 1 00:00:ecc. ecc.
.....

and having a unique file or having an output of separate files as proposed in my question. But how I can do this with scripting?

vgersh99 · April 4, 2007, 12:59pm

GET () { 
     while read Host
     do
         snmpwalk -Onq $Host $Pass $1 | sed -e s/^$1\.// -e s/\"//g | sort >> "/tmp/${Host}.txt"
     done < "${HostFile}"
   }

you'll have ONE file per host stored under '/tmp' directory.
As far as the parameters going in the 'wrong' place.... you'll have to troubleshoot it yourself: make sure your 'snmpwalk' signature jives with what you're scripting it with - should not be too bad!

CM64 · April 4, 2007, 1:14pm

About troubleshoothing you are right, you have helped so much so it's better that I do something as well, but I have no files, one per host in /tmp. I suspect that the error is due to this line of code

for OID in Mac Port
do eval "GET \$$OID > /tmp/${Script}\$$OID" ; done

indeed in /tmp I have something like

nameofscript.1.3.6.1.2.1.17.4.3.1.1

one for OID, when I use your

while read Host
do

nameofscript.OID is rewritten for every host in hostfile and so I don't have multiple entries. Correct? Can you be very patient and give me some more advice?

vgersh99 · April 4, 2007, 1:25pm

how about this:

GET () { 
     while read Host
     do
         snmpwalk -Onq $Host $Pass $1 | sed -e s/^$1\.// -e s/\"//g | sort >> "/tmp/${Host}.txt"
     done < "${HostFile}"
   }  

for OID in Mac Port
do eval "GET \$$OID" ; done

I just removed the file redirection from the 'for' loop, but you might need it for your 'JOIN'. WIth the redirection removed you should get ONE file (for both Mac and Port) for ONE host all in ONE file. I'm not sure if that's what you're looking for as a final result.

CM64 · April 4, 2007, 1:37pm

Tomorrow I will try your last suggestion. A unique file is good, but I have to prepend to every line of output the ip of the swith, if not I know where is the port and the associated mac but I don't know where among many switches. So at first look something is missing in your last lines of code.

Shell_Life · April 4, 2007, 1:40pm

anbu, thank you for noticing the problem -- I tested using my our set of test data.

In any event, here is a solution that will work with the original post using the
original data:

### Remove unwanted string from the update file.
sed 's/.* $.*$/\1/' upd_file > short_upd_file

### Create matched records: in update file and also in the master file.
egrep -f short_upd_file master_file > matched_file

### Create not matched records: in update file and not in matched file.
egrep -v -f matched_file short_upd_file > tmp_not_matched_file

### Add "NOT FOUND" to the end of not matched records.
sed 's/$/NOT FOUND/' tmp_not_matched_file > not_matched_file

### Merge and sort all records into solution file.
sort matched_file not_matched_file > solution_file