I'm using a modified version of Gareth Bromley's PERL script to put things
into a MySQL database. It does require a logswitch and export to ascii.
## BEGINNING OF SCRIPT
#!/usr/bin/perl
#
# Created: Gareth Bromley <[EMAIL PROTECTED]>, 26th Sep 2001
# Description: FW1 log converter and DB importer
# ------------------------------------------------------------------------
# Modified: Erick Kinnee <[EMAIL PROTECTED]>, 10/Jan/2001
# Added: SQL script to crEate the table.
# Fixed the script bombing out if it encounters a "'" in the sysmsg
# field. I was seeing this on SAM entries.
# Added a counter that outputs the number of lines processed
(useless, I like to see progress though)
# Added variables for ALL the fields, my log has more than the
# original script accounted for.
# Function that converts the date and time fields to a valid MySQL
# DATETIME format. I prefer it this way, dunno about you.
#
# Takes FW-1 log exports, made with fw logexport using defaults, and puts
# into SQL, CSV or ......??
#
# Options exist to:
# --csv Data exported in CSV
# --sql Data exported as SQL
#
# --rule Only export Rule Number X
# --dst Only export traffic to host A
# --src Only export traffic from host B
# --dstport Only export traffic to port Y
# --srcport Only export traffic from port Z
#
# --dbistr DBI Connection string to use
# --dbiuser DBI Username to use
# --dbipass DBI password to use
#
# --file Log file to manipulate
#
############################################################################
# create table dailylog (
# recnum int(20) not null auto_increment,
# datetime datetime,
# orig varchar(15),
# type varchar(10),
# action varchar(10),
# alert varchar(8),
# ifname varchar(20),
# ifdir varchar(8),
# proto varchar(5),
# src varchar(15),
# dst varchar(15),
# dstport varchar(20),
# srcport varchar(20),
# len varchar(4),
# rule varchar(4),
# icmptype int(10),
# icmpcode varchar(10),
# sysmsgs varchar(50),
# primary key (recnum));
############################################################################
use Getopt::Long;
use DBI;
# Subroutines relevant to this system
sub Usage()
{
print "usage: $ARGV[0] [--csv | --sql] --file <NAME OF FILE>\n";
print "\n";
print "Additional options exist to manipulate the export\n";
print "--rule X\tExport events logged by rule X\n";
print "--dst A\tExport events to host A\n";
print "--src B\tExport events from host B\n";
print "--dstport Y\tExport events to IP port Y\n";
print "--srcport Z\tExport events from IP port Z\n";
print "\n";
print "For database access the following options may be specified\n";
print "--dbistr Q\tThe DBI connection string to use\n";
print "--dbiuser O\tThe DBI username to use\n";
print "--dbipass P\tThe DBI username password to use\n";
print "--auto\tDon't import the Firewall-1 row ID\n";
print "\n";
exit 0;
}
# Globals ;)
my $searchcriteria = 0;
my $lineinterest = 0;
my $dbistr = "DBI:mysql:fw1";
my $dbiuser = "";
my $dbipass = "";
# process cmd line arguments
%cmdopts = ();
GetOptions(\%cmdopts,'csv','sql','rule:s','dst:s','src:s','dstport:s','srcpo
rt:s','file:s','dbistr:s','dbiuser:s','dbipass:s','auto');
# Process options, and check for sanity
if ($cmdopts{'csv'} ne "") {
# Use Comma seperated Value file of network names, ip address and subnet
$csvfile = 1;
if ($cmdopts{'sql'} ne "") {
Usage();
}
} elsif ($cmdopts{'sql'} ne "" ) {
# Use tab delimited file
$database = 1;
if ($cmdopts{'dbistr'} ne "") {
$dbistr = $cmdopts{'dbistr'};
}
if ($cmdopts{'dbiuser'} ne "") {
$dbiuser = $cmdopts{'dbiuser'};
}
if ($cmdopts{'dbipass'} ne "") {
$dbipass = $cmdopts{'dbipass'};
}
} else {
Usage();
}
if ($cmdopts{'file'} eq "") {
# No filename specified
Usage();
}
# Define how many matches must meet for line to be of interest
if ($cmdopts{'rule'} ne "") {
$searchcriteria++;
}
if ($cmdopts{'dst'} ne "") {
$searchcriteria++;
}
if ($cmdopts{'src'} ne "") {
print "$cmdopts{'src'}\n";
$searchcriteria++;
}
if ($cmdopts{'dstport'} ne "") {
$searchcriteria++;
}
if ($cmdopts{'srcport'} ne "") {
$searchcriteria++;
}
if ($cmdopts{'csv'}) {
# Extract new filename from supplied name
#
($fw,$junk) = split(/_/,$cmdopts{'file'});
($date,$hour,$ext) = split(/-/,$junk);
$outfile = ">".$date.".csv";
open(OUTFILE,$outfile) or die "ERROR: Can't open output file
$outfile\n";
# Print out header line
print OUTFILE
"num,date,time,orig,type,action,alert,ifname,ifdir,proto,src,dst,dstport,src
port,len,rule,icmp-type,icmp-code,sys_msgs";
} else {
# Open a connection the DBI compatibale database supplied in $dbistring
$dbh = DBI->connect($dbistr,$dbiuser,$dbipass) or die "ERROR: Couldn't
open database: '$DBI::errstr;' stopped";
}
# Format of file should be:
# field1;field2;.....;fieldN
open(LOGFILE,$cmdopts{'file'}) or die "ERROR: Can't open file
$cmdopts{'file'}\n";
chomp;
$count = 0;
while (<LOGFILE>) {
#skip the header line that appears in the log file.
$count += ($_ =~ tr/\n//);
if ( $count > '1' ) {
# Split data into parts based on delimiter
($recnum,$date,$time,$orig,$type,$action,$alert,$intface,$ifdir,$proto,$src,
$dst,$dstport,$srcport,$rule,$reason,$len,$xlatesrc,$xlatedst,$xlatesport,$x
latedport,$icmptype,$icmpcode,$ikelog,$product,$message,$user,$port,$reason,
$negid,$sysmsg) = split(/\;/);
# Just for reference the line below is the field names from the actual
exported log file
#num;date;time;orig;type;action;alert;i/f_name;i/f_dir;proto;src;dst;service
;s_port;rule;reason:;len;xlatesrc;xlatedst;xlatesport;xlatedport;icmp-type;i
cmp-code;IKE Log:;product;message;user;port:;reason;Negotiation Id:;sys_msgs
# Remove trailing UNIX and MSDOS carriage return and newlines and
oddball single quotes whitin $sysmsg
$sysmsg =~ s/\r//;
$sysmsg =~ s/\n//;
$sysmsg =~ s/\'//g;
&fixdatetime($date, $time);
# Work out what to print if its interesting
if ($cmdopts{'rule'} ne "" && $cmdopts{'rule'} eq $rule) {
# This line may be of interest
$lineinterest++;
}
# Work out what to print if its interesting
if ($cmdopts{'dst'} ne "" && $cmdopts{'dst'} eq $dst) {
# This line may be of interest
$lineinterest++;
}
# Work out what to print if its interesting
if ($cmdopts{'src'} ne "" && $cmdopts{'src'} eq $src) {
# This line may be of interest
$lineinterest++;
}
# Work out what to print if its interesting
if ($cmdopts{'dstport'} ne "" && $cmdopts{'dstport'} eq $dstport) {
# This line may be of interest
$lineinterest++;
}
# Work out what to print if its interesting
if ($cmdopts{'srcport'} ne "" && $cmdopts{'srcport'} eq $srcport) {
# This line may be of interest
$lineinterest++;
}
# Does this line meet our selection criteria?
if ($lineinterest == $searchcriteria) {
if ($cmdopts{'csv'}) {
# Dump out to a CSV file
print OUTFILE
"$recnum,$datetime,$orig,$type,$action,$alert,$intface,$ifdir,$proto,$src,$d
st,$dstport,$srcport,$len,$rule,$icmptype,$icmpcode,$sysmsg\n";
} else {
# Must be inserted into database
if ($cmdopts{'auto'}) {
# Don't import Firewall-1 record number field, as its auto
created by DB
#my $sqlstr = "INSERT into dailylog
(date,time,orig,type,action,alert,ifname,ifdir,proto,src,dst,dstport,srcport
,len,rule,icmptype,icmpcode,sysmsgs) VALUES
('$date','$time','$orig','$type','$action','$alert','$intface','$ifdir','$pr
oto','$src','$dst','$dstport','$srcport','$len','$rule','$icmptype','$icmpco
de','$sysmsg')";
my $sqlstr = "INSERT into dailylog
(datetime,orig,type,action,alert,ifname,ifdir,proto,src,dst,dstport,srcport,
len,rule,icmptype,icmpcode,sysmsgs) VALUES
('$datetime','$orig','$type','$action','$alert','$intface','$ifdir','$proto'
,'$src','$dst','$dstport','$srcport','$len','$rule','$icmptype','$icmpcode',
'$sysmsg')";
my $sth = $dbh->prepare($sqlstr);
$sth->execute();
} else {
# Import verbatim
my $sqlstr = "INSERT into dailylog VALUES
('$recnum','$datetime','$orig','$type','$action','$alert','$intface','$ifdir
','$proto','$src','$dst','$dstport','$srcport','$len','$rule','$icmptype','$
icmpcode','$sysmsg')";
my $sth = $dbh->prepare($sqlstr);
$sth->execute();
}
}
}
$lineinterest = 0;
chomp;
print("$count\r");
}
}
if ($cmdopts{'sql'}) {
$dbh->disconnect();
}
############################################################################
sub fixdatetime #01/10/02 10:21:AM EMK
############################################################################
{
#Change things like "19Sep2001" to 2001/9/19 for MYSQL DATETIME
#Just change the order of what gets assigned to $date below and it'll
#work for MS SQL's datetime format.
%mon2num = qw(
jan 1 feb 2 mar 3 apr 4 may 5 jun 6
jul 7 aug 8 sep 9 oct 10 nov 11 dec 12
);
#this sucks since FW-1 seems to log the first of the month a "1" not
"01"
# as I'd like it too... So we have to compensate for this braindead
# behavior.
if ( length($date) > 8 ) {
$monnum = $mon2num{ lc substr($date, 2, 3)};
}
elsif ( length($date) <= 8) {
$monnum = $mon2num{ lc substr($date, 1, 3)};
}
$daynum = substr($date, 0, 2);
$yearnum = substr($date, 5, 4);
$date = "$yearnum\/$monnum\/$daynum";
#$date = "$daynum\/$monnum\/$yearnum";
#Stick the fixed date and the time back together for a SQL usable
# field
$datetime = "$date"." $time";
return($datetime);
} ##fixdatetime
## END OF SCRIPT
-----Original Message-----
From: Jason Stout [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 23, 2002 11:52
To: [EMAIL PROTECTED]
Subject: [FW-1] Logging to a database
Does anyone know of a way to log to a SQL database BUT also log to fw.log as
well? I would think it would be easy to use a user defined alert similar to
Spitzner's alert.sh script however we still need to be able to use Check
Point's logviewer for troubleshooting/diagnostics. The thought of writing a
frontend to query the SQL server for troubleshooting has crossed my mind but
I'd rather avoid that due to resource issues.
Thanks,
Jason Stout
=================================================
To set vacation, Out Of Office, or away messages,
send an email to [EMAIL PROTECTED]
in the BODY of the email add:
set fw-1-mailinglist nomail
=================================================
To unsubscribe from this mailing list,
please see the instructions at
http://www.checkpoint.com/services/mailing.html
=================================================
If you have any questions on how to change your
subscription options, email
[EMAIL PROTECTED]
=================================================
=================================================
To set vacation, Out Of Office, or away messages,
send an email to [EMAIL PROTECTED]
in the BODY of the email add:
set fw-1-mailinglist nomail
=================================================
To unsubscribe from this mailing list,
please see the instructions at
http://www.checkpoint.com/services/mailing.html
=================================================
If you have any questions on how to change your
subscription options, email
[EMAIL PROTECTED]
=================================================
