--- sysplogd	2009/04/11 08:30:33	5
+++ sysplogd	2009/04/11 11:45:36	12
@@ -6,36 +6,106 @@
 use IO::Socket;
 use Data::Dump qw/dump/;
 use DBI;
+use Getopt::Long;
 
-my $port = 514;
+our $port = 514;
+our $MAXLEN = 1524;
 
-my $MAXLEN = 1524;
+our $dsn    = 'DBI:Pg:dbname=syslog';
+our $user   = 'dpavlin';
+our $log    = '/tmp/sysplog.log';
 
-my $dsn = 'DBI:Pg:dbname=syslog;host=llin.lan';
-my $user = 'dpavlin';
-my $debug = 0;
+require 'conf.pl' if -e 'conf.pl';
+
+my $debug  = 0;
+my $schema = 0;
+
+GetOptions(
+	'debug+'  => \$debug,
+	'schema!' => \$schema,
+	'log=s'   => \$log,
+	'port=i'  => \$port,
+) || die "usage: $0 --debug --schema\n";
+
+our $VERSION = '0.00';
+
+my $sql_schema = q{
+
+CREATE TABLE facilities (
+	id		serial,
+	name		text,
+
+	PRIMARY KEY(name)
+);
+
+CREATE TABLE log (
+	id		serial,
+	timestamp	timestamp default now(),
+	ip		inet not null,
+	hostname	text not null,
+	message		text,
+	level		int,
+	facility	int,
+	program		text,
+	pid		int,
+
+	PRIMARY KEY (id)
+);
+
+};
+
+
+my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) || die $DBI::errstr;
+
+if ( $schema ) {
+	$dbh->begin_work;
+
+	$dbh->do( $_ ) foreach split(/;/, $sql_schema);
+
+	my $sth = $dbh->prepare( q{
+		insert into facilities (name) values (?)
+	});
+
+	$sth->execute( $_ ) foreach ( qw/
+		kernel user mail system security internal
+		printer news uucp clock
+		security2
+		ftp ntp
+		audit alert
+		clock2
+		local0 local1 local2 local3 local4 local5 local6 local7
+	/ );
+
+	warn "# created sql schema\n";
+
+	$dbh->commit;
+}
+
+my $sth_log_full = $dbh->prepare(qq{
+	insert into log
+	(ip,hostname,message,level,facility,program,pid)
+	values (?,?,?,?,?,?,?)
+});
+
+my $sth_log_unparsed = $dbh->prepare(qq{
+	insert into log (ip,hostname,messsage) values (?,?,?)
+});
 
-my @facilities = ( qw/
-kernel user mail system security internal printer news uucp clock security2 
-FTP NTP audit alert clock2 local0 local1 local2 local3 local4 local5 local6 local7
-/ );
 
-# Start Listening on UDP port 514
 my $sock = IO::Socket::INET->new(
 	LocalPort => $port,
 	Proto => 'udp'
 #	ReuseAddr => 1,
 ) || die "can't listen to $port: $!";
 
-my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) || die $DBI::errstr;
-
-my $sth = $dbh->prepare(qq{
-	insert into log
-	(ip,hostname,facility,priority,level,program,message) values
-	(? ,?       ,?       ,?       ,?    ,?      ,?      )
-});
+open(my $log_fh, '>>', $log) || die "can't open log $log: $!";
+$log_fh->autoflush(1);
+sub _log {
+	warn 'LOG ',dump( @_ ), $/ if $debug;
+	print $log_fh time() . '|' . join('|', @_), $/;
+}
 
-print "INFO: listen on $port",$/;
+_log "INFO: listen on $port";
 
 my $rin = '';
 my $buf;
@@ -44,13 +114,18 @@
 	my ($port, $ipaddr) = sockaddr_in($sock->peername);
 	my $hostname = gethostbyaddr($ipaddr, AF_INET);
 	my $ip = join('.', unpack('C4',$ipaddr));
-	warn "# ",dump( $port, $ipaddr, $hostname, $buf );
+	my @values = ( $ip, $hostname, $buf );
 
 	if ( $buf =~ /<(\d+)>\s*(\S*)\s*:\s*(.*)/ ) {
-		my $level=$1 % 8;
-		my $fac=($1-$level) / 8;
-		$sth->execute( $ip, $hostname, $fac, $1, $level, $2, $3 );
+		$values[2] = $3;
+		my $level    = $1 % 8;
+		my $facility = ( $1-$level ) / 8;
+		my $program  = $2;
+		my $pid      = $1 if $program =~ s/\[(\d+)\]$//;
+		push @values, ( $level, $facility, $program, $pid );
+		$sth_log_full->execute( @values );
 	} else {
-		$sth->execute( $ip, $hostname, undef, undef, undef, undef, $buf );
+		$sth_log_unparsed->execute( @values );
 	}
+	_log( @values );
 }