Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 0 down vote accepted
  • 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.