1

I'm looking to keep arpwatch entries in a mysql database to crossreference with other information I'm storing based on mac addresses. I've manually imported the arpwatch database into my mysql database, but being a novice with databases I'm not sure what the best way to continually update the database with new entries without creating duplicates would be. None of the fields can be unique, as even the time is duplicated frequently. I'm not interested in the actual arpwatch events like flip flop or new station, just the mac/ip/time pairings.

Would a simple bash (or sql) shell script do the trick? Would it be possible to make the mac address plus the time be a composite key of some sort?

  • the database is called utility, table is arpwatch, columns are mac, ip, time
  • a seperate table named 'hosts' with columns mac, ip, type, hostname, location, notes has mac as the primary key. This table will correlate different ip addresses that a mac had over time using the arpwatch column
  • initial import was done with MySQL Workbench using INSERT INTO commands with creative search and replace on the text file
0

1 Answer 1

0
  • set mac,ip,time to be composite primary key

  • Created awk script: arpwatch_to_sql.awk


#!/usr/bin/awk -f
BEGIN {
        OFS=","
        FS="\t"
        print "INSERT IGNORE INTO utility.arpwatch (mac,ip,time) VALUES"
}
{print "('"$1"'", "'"$2"'", "'"$3"'""),"}

  • command to import updates:

./arpwatch_to_sql.awk /var/lib/arpwatch/arp.dat > ~/updatearp.sql
mysql -vvvv -h [sql hostname] -P 3306 -u [username] -p [password] <updatearp.sql 2>&1 1>&2|echo `date` `grep 'Query\|Records'`>>updatearp.log

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .