$Id: README,v 1.13 2001/08/23 04:56:58 xant Exp $
(These notes are slightly PostGres-centric. However, anything produced by or included with packet2sql can easily by adapted to other database engines.)
NOTE: When I say "as dbuser", it means you have to be logged in as a user with permission to create a database and insert rows into tables. You can either log in as that user, or you can do the following:
su -l dbuser -c 'the command I give you'
- Installation
Create a database with any name you like. In postgres, as dbuser, do the following:
createdb packets
Next, execute the DDL (sql/packet2sql-ddl.sql) to create the table:
psql -f sql/packet2sql-ddl.sql packets
You now have a packets database with a logged_packets table. Compile the sources:
make
Finally, copy packet2sql to somewhere in your path:
cp packet2sql /usr/bin/packet2sql
- Use
If called with no options, packet2sql will read the log input from stdin. Since syslog dates do not include the year, packet2sql will assign one. If the month of a log entry comes after the current month, it assigns the previous year, otherwise it assigns the current year. This is helpful for processing December's logs in January. It is also helpful for people who archive logs and wish to recreate them. You can also use the '-y' option to override the month guessing heuristic and assign one yourself. The option '-y 1998' will assign the year 1998 to all log files specified after the -y option. Note that if you use the -y option and wish to read log entries from stdin, you must specify '-' as the file to read from.
Determine where your packet logs are kept (usually /var/log/messages). As a user permitted to read the system logs (probably root), run
packet2sql /var/log/messages > log`date +%Y%m%d`.sql This is not a quick process. Packet2sql will produce logYYYYmmdd.sql where YYYYmmdd is the current date. To execute the SQL, as dbuser:
psql -f logYYYYmmdd.sql packets
You will see a lot of rows being inserted. This goes pretty fast. You may see
an error like the following:
psql:packet2sql/log20000609.sql:15: ERROR: Cannot insert a duplicate key into unique index logged_packets_pk
This is OK - it means you tried to insert a packet that's already in the database.
- Applications
Some useful things to do with packet logs (in psql):
SELECT sourcehost, count(*) from logged_packets
WHERE chain='input'
GROUP BY sourcehost ORDER BY count;
(Creates a sorted list of everyone who's been logged trying to access your
machine, along with the number of times they've tried it. If you direct the
output to a file, it's pretty easy to do an nslookup or even a whois on each
site.)
SELECT sourcehost, destport, count(*) from logged_packets
WHERE chain='input'
GROUP BY sourcehost ORDER BY count;
(Same thing, but tells you what ports the putative attackers are trying to
access.)
SELECT ... from logged_packets WHERE proto='udp' (Select only udp packets.)
SELECT ... from logged_packets WHERE chain='output' (Select only packets logged by the output chain.)
Fun With Logs:
Another useful thing to do is have packet2sql dump directly into a database directly from the syslog, without an intermediate step! To do this, you need to do something like this as root:
Modify /etc/syslog.conf and add:
kern.* |/var/log/kernel
$ (Restart syslogd.) ...
$ pushd /var/log
$ (If kernel logfile already exists, mv it to another filename.) ...
$ mknod kernel p
$ popd
$ nohup ./suckpackets &
syslogd will copy all kernel messages directly to a named pipe. Then
suckpackets reads from the pipe and sends everything to packet2sql, then to
psql which executes it against the database as the specified user. You will
want to make sure /var/log/kernel has the correct permissions (rw only by
root). suckpackets could be run from rc.local if you want to make sure it's
always running.
** Make sure to set DBUSER at the top of suckpackets before running it. **
Contact info:
Please send email to the following address with any questions, comments, defect reports, diff patches, requests to be my best friend, or other useful messages. Please refrain from sending useless messages. Thank you ;)
Maintainer: Xant (xant at users.sourceforge.net)
Web page: http://packet2sql.sourceforge.net Download: http://sourceforge.net/project/showfiles.php?group_id=6781 CVS info: http://sourceforge.net/cvs/?group_id=6781