#!/usr/bin/perl -w
# $Id: ipfw2sql,v 1.1 2003/05/01 23:26:41 candy Exp $
use strict;
use POSIX;
use Time::Local;
use Getopt::Std;
use Pg; # ports/database/p5-Pg

my $pg_host = "localhost";
my $pg_user = "pgsql";
my $pg_pass = "";
my $pg_db = "pgsql";

sub nz($) {
	return defined($_[0]) ? $_[0] : "";
}

sub autoflush($$) {
	my($so, $flag) = @_;
	select((select($so), $| = $flag)[0]);
}

sub errmsg($) {
	my($e) = @_;
	return "PGRES_EMPTY_QUERY" if ($e eq PGRES_EMPTY_QUERY);
	return "PGRES_COMMAND_OK" if ($e eq PGRES_COMMAND_OK);
	return "PGRES_TUPLES_OK" if ($e eq PGRES_TUPLES_OK);
	return "PGRES_COPY_OUT" if ($e eq PGRES_COPY_OUT);
	return "PGRES_COPY_IN" if ($e eq PGRES_COPY_IN);
	return "PGRES_BAD_RESPONSE" if ($e eq PGRES_BAD_RESPONSE);
	return "PGRES_NONFATAL_ERROR" if ($e eq PGRES_NONFATAL_ERROR);
	return "PGRES_FATAL_ERROR" if ($e eq PGRES_FATAL_ERROR);
	return sprintf("PGRES_UNKNOWN(%d)", $e);
}

sub post1($@) {
	my($table, @v) = @_;
	my $conn_arg = "host=$pg_host dbname=$pg_db user=$pg_user password=$pg_pass";
	my $conn = Pg::connectdb($conn_arg);
	if($conn->status == PGRES_CONNECTION_OK) {
		my $query = "INSERT INTO $table VALUES(";
		my $i;
		for ($i = 0; $i <= $#v; $i++) {
			$query .= "'" . $v[$i] . ($i == $#v ? "'" : "',");
		}
		$query .= ");";
		# printf("Q:%s\n", $query);
		my $result = $conn->exec($query);
		if ($result->resultStatus == PGRES_COMMAND_OK) {
		}
		else {
			printf(STDERR "Pg::exec: %s\n", $query);
			printf(STDERR "Pg::exec: %s\n", errmsg($result->resultStatus));
			printf(STDERR "Pg::exec: %s\n", $conn->errorMessage);
		}
	}
	else {
		printf(STDERR "Pg::connectdb: %s\n", $conn->errorMessage);
	}
}

my %mon = (
	"Jan" => 1,
	"Feb" => 2,
	"Mar" => 3,
	"Apr" => 4,
	"May" => 5,
	"Jun" => 6,
	"Jul" => 7,
	"Aug" => 8,
	"Sep" => 9,
	"Oct" => 10,
	"Nov" => 11,
	"Dec" => 12,
);

my %proto = (
	"ip" => 0,
	"icmp" => 1,
	"tcp" => 6,
	"udp" => 17,
	"ipv6" => 41,
	"gre" => 47,
);

sub parse($) {
	my($lbuf) = @_;
	my @v = split(/ +/, $lbuf);
	my @ret = ();
	my($m, $d, $HMS, $deny, $proto, $sip, $sport, $dip, $dport);
	if ($#v >= 13) {
		my $ok = 0;
		if ($#v >= 18) {
			if ($v[5] =~ /^ipmon/) {
				# Apr  d HH:MM:SS host filter.warn    ipmon[69]: HH:MM:SS.uuuuuu              si0 @0:27 b xx.xx.xx.xx,xx -> xx.xx.xx.xx,xx PR tcp len 20 40 -S IN
				$m = $mon{$v[0]};
				$d = $v[1];
				$HMS = $v[6];
				my $x0 = ($v[8] =~ /^@/) ? 10 : 11;
				my $src = $v[$x0];
				my $dst = $v[$x0 + 2];
				my $p = $v[$x0 + 4];
				$p =~ tr/A-Z/a-z/;
				#printf("proto %s\n", $p);
				$proto = defined($proto{$p}) ? $proto{$p} : 0;
				$deny = 1;
				($sip, $sport) = split(/,/, $src . ",0");
				($dip, $dport) = split(/,/, $dst . ",0");
				$ok = 1;
			}
		}
		else {
			if ($v[5] =~ /^ipfw/) {
				# Apr dd HH:MM:SS host /kernel: ipfw: 5300 Deny UDP xx.xx.xx.xx:xx xx.xx.xx.xx:xx out via fxp0
				$m = $mon{$v[0]};
				$d = $v[1];
				$HMS = $v[2] . ".000000";
				$deny = $v[7] =~ /Deny/i;
				my $p = $v[8];
				my $src = $v[9];
				my $dst = $v[10];
				$p =~ tr/A-Z/a-z/;
				$proto = defined($proto{$p}) ? $proto{$p} : 0;
				($sip, $sport) = split(/:/, $src . ":0");
				($dip, $dport) = split(/:/, $dst . ":0");
				$ok = 1;
			}
		}
		if ($ok) {
			@ret = ($m, $d, $HMS, $deny, $proto, $sip, $sport, $dip, $dport);
		}
	}
	return @ret;
}

sub nain($) {
	my($copy) = @_;
	my $Y = (localtime())[5] + 1900;
	my $Z = mktime((0,0,0,1,0,0));
	my $t0 = timegm(0,0,0,1,1,100);
	my $t1 = mktime(0,0,0,1,1,100);
	my $tz = ($t0 - $t1) / 3600;
	my $lbuf;
	my $lno = 1;
	autoflush(\*STDOUT, 1);
	while ($lbuf = <>) {
		chomp($lbuf);
		my($m, $d, $HMS, $deny, $proto, $sip, $sport, $dip, $dport) = parse($lbuf);
		if (defined($m)) {
			if ($copy) {
				printf("%d-%d-%d %s+%d\t%d\t%d\t%s\t%d\t%s\t%d\n", $Y, $m, $d, $HMS, $tz, $deny, $proto, $sip, $sport, $dip, $dport);
			}
			else {
				my $date = "$Y-$m-$d $HMS+$tz";
				post1("ipfw", $date, $deny, $proto, $sip, $sport, $dip, $dport);
			}
		}
		else {
			# printf(STDERR "%d: parse error: %s\n", $lno, $lbuf);
		}
		$lno++;
	}
	return 0;
}

my $usage =
	"usage: %s [-c][-d database][-h host][-U user][-W password]\n"
	. "\t-c print only. (for COPY)\n"
	;

sub main() {
	my($ex);
	$ex = 1;
	getopts('cd:h:SU:VW:');
	nz($::opt_c);
	nz($::opt_V);
	if (defined($::opt_V)) {
		printf(STDERR $usage, $0);
	}
	else {
		defined($::opt_d) && ($pg_db = $::opt_d);
		defined($::opt_h) && ($pg_host = $::opt_h);
		defined($::opt_W) && ($pg_pass = $::opt_W);
		defined($::opt_U) && ($pg_user = $::opt_U);
		nain($::opt_c);
		$ex = 0;
	}
	return $ex;
}

exit main();