I am trying to get some data from my logs into a postgres database. Included in the program is the 'data' I am slurping, under 'data'. I know that the way it is written it won't work, but it works fine from standard in. I get the following error message when running the program:
DBI::st=HASH(0x8757070)->_prepare(...): attribute parameter '10.1.0.1' is not a hash ref at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBD/Pg.pm line 190, <> line 1.
The database table looks like this:
---------------+-----------------------------+------------------------
+------------------------------------
date_combined | timestamp without time zone |
origin | integer |
action | text |
if_name | text |
if_dir | text |
src | inet |
dst | inet |
proto | text |
rule | integer |
service | integer |
s_port | integer |
icmp | text |
icmp_type | text |
primary_key | bigint | not null default nextva
+l('logs_primary_key_seq'::regclass)
When I try the following code:
#!/usr/bin/perl -w
use DBI;
my $dbname = 'logs';
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname");
if (!$dbh) {
die "Err Couldn't open connection: ".$DBI::errstr."\n";
}
my $insert_query = "INSERT INTO logs (origin, action, if_name, if_dir,
+ src, dst, proto, rule, service,s_port, icmp_type, icmp_code) values
+(?,?,?,?,?,?,?,?,?,?,?,?,?)";
$datematch = qr/(\d{1,2})([a-z]{3})(\d{4})/io;
%months = (
Jan => "January",
Feb => "February",
Mar => "March",
Apr => "April",
May => "May",
Jun => "June",
Jul => "July",
Aug => "August",
Sep => "September",
Oct => "October",
Nov => "November",
Dec => "December"
);
while (<>) {
my @fields = split (/\|/);
$i++;
# remove the leading space padding and reformat the date field
$fields[1] =~ s/^\ //;
$fields[1] =~ s/$datematch/$months{$2}\ $1\,$3/;
my $date = "$fields[1] $fields[2]";
my $date = "$fields[1] $fields[2]";
foreach $field (3, 11, 12) {
if ($fields[$field] eq "") {
$fields[$field] = "0.0.0.0";
}
}
for ($i = 1; $i < $#fields; $i++) {
$fields[$i] =~ s/\'//g;
}
my $numrows = $dbh->do($insert_query,$date,$fields[3],$fields[5],$fiel
+ds[7],$fields[8],$fields[11],$fields[12],$fields[13],$fields[14],$fie
+lds[15],$fields[16],$fields[17],$fields[18]);
if (!$numrows) {
die "Failed to insert entry number $i:".$dbh->errstr."\n";
}
@fields="";
}
_DATA
2|9Oct2007|23:59:00|1.13.229.61|log|accept||eth-s1/s2p1c0|inbound|VPN-
+1 & FireWall-1||1.19.14.22|10.21.21.8|tcp|21|80|1326|||||||||||||||||
+||||
"Two Wheels good, Four wheels bad."
|