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 separate 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
Best Answer
Sure, a simple script can parse the results of the tool and insert rows into the database.
You can make composite unique keys in mysql:
If you try to insert a dup, it will fail (but will you ever have a duplicate time?).