[syslog-ng]PIX 2 MySQL

[Bill Nash]

On Fri, 29 Oct 2004, Cary, Kim wrote:

> I just got logging going with syslog-ng in the last couple weeks (first 
> client is our PIX 520). We can have up to 20Gb/day from our PIX. When 
> compressed, the logs are up to 2Gb/day. We do want a record of all sessions 
> for forensics & troubleshooting (already saved us hours of time) but the log 
> format is quite verbose. Because of that verbosity, I was thinking of writing 
> just key fields to a MySQL database as you suggest. However, I don't want to 
> get into a situation where the only reporting is whatever report script I 
> have time to write... If Joseph or someone else has a suggestion for fields 
> to insert into the db and a reporting package to use, I'd appreciate it.

As far as relevent fields, the PIX is going to log everything to facility 
local4, with varying severity. Indexing on those may or may not get you 
anywhere. Luckily, PIX log messages are all well prefixed by message 
'class', using a standard form. Some perl regexp examples I use for 
handling PIX traffic:

# Simple tcp denies
^.*PIX-4-106023: Deny tcp src (.*) dst (.*) by access-group "(.*)"

# IP spoofing shenanigans
^.*PIX-2-106017: Deny IP due to Land Attack from (.*) to (.*)
^.*PIX-2-106016: Deny IP spoof from (.* to .* on interface .*)

# Untranslated NAT traffic, usually worm and portscan backscatter
^.*PIX-3-106011: Deny inbound \(No xlate\) tcp src outside:(.*) dst outside:(.*)\/80

Given the fairly predictable formatting from PIX output, you could match 
something as simple as:
($level, $msgid, $output) =~ $message =~ /^.*PIX-(\d)-(\d+): (.*)/;

And then insert and index your db table accordingly. Please note, these 
are purely perl examples (I use a perl based rules analysis engine for 
my incoming syslog traffic), but are relevent to anyone using perl for log 
processing.

As far as reporting goes, this is going to take time and effort on your 
part, to determine what's relevent and how to package it. Being able to 
store and classify your PIX traffic by msgid helps quite a bit, though, 
once you know which fields you're looking for. Again, the consistent 
format of PIX output is a boon on this front. Going on the assumption that 
you're storing all of your logs and doing post-processing for your 
reporting (or forensics):

#!/usr/bin/perl
# billn was here. =)

use DBI;
$dbh = DBI->connect("DBI:mysql:pixdb:127.0.0.1", "billn", "likesbeer");
$insert = $dbh->prepare("insert into pixlog (date_source, level, msgid, 
output) values (?, ?, ?, ?) ");

open (IN, @ARGV[0]); # supply a file name as command line argument.
while(<IN>) {
     chomp; $message = $_;
     if( ($date_source, $level, $msgid, $output) =~ $message =~ /^(.*) 
%PIX-(\d)-(\d+): (.*)/ ) {
            $insert->execute($date_source, $level, $msgid, $output);
     }
}
close(IN);
$dbh->disconnect;

#---

Caveat emptor. I wrote this off the top of my head and have not checked 
that it actually parses correctly. It's a good, simple example, though.
Date mangling was left out of this example for brevity and expediancy.

Flipping it around, though, you could do reporting with the above example 
regexp's, like so:

$sth = $dbh->prepare("select output from pixlog where msgid = ?");
if ($rv = $sth->execute('106023') ) {
     while (($output) = $sth->fetchrow)
       if ($output = /Deny tcp src (.*)/(\d+) dst (.*)/(\d+) by access-group "(.*)"/) {
             $src_ip = $1; $src_port = $2;
             $dst_ip = $3; $dst_port = $4;
             $ACL_name = $5;

           # Your preferred output or data organization here.
     }
}

Hope this helps. =)

- billn