Re: Snort Archive

[Brett.Gillett () tsx ca]

Here it is,

SQL statement (I am by no means a SQL expert - this may be the problem
right here) - the idea is that this SQL select runs every night to grab the
days data. you can see in the select,
what I want to reduce the data to.

 my $DExtractSql = "select event.timestamp,
                event.sid as sid,
                iphdr.ip_src as ip_src,
                tcphdr.tcp_sport as sport,
                iphdr.ip_dst as ip_dst,
                tcphdr.tcp_dport as dport,
                iphdr.ip_proto as proto,
                signature.sig_name as sig
                from event,signature,iphdr,tcphdr
                where event.signature = signature.sig_id
                        and event.cid = iphdr.cid
                        and event.cid = tcphdr.cid
                        and dayofmonth(event.timestamp) = ?
                        and month(event.timestamp) = ?";

Here the perl script section - orginally I wrote the information directly
to the secondary DB, but I thought that may be the problem, so I changed
script to write to a file first.- even then I don't seem to get all the
entries - which leads me to think it's something in the SQL statement.

my $DExtractSqlSth = $dbh->prepare( $DExtractSql );
$DExtractSqlSth->execute($day,$month);
open (WORKFILE,">/tmp/work.file");
while (($timestamp,$sid,$ip_src,$sport,$ip_dst,$dport,$proto,$sig)
= $DExtractSqlSth->fetchrow_array) {
     print WORKFILE
"$timestamp,$sid,$ip_src,$sport,$ip_dst,$dport,$proto,$sig\n";
 }
close (WORKFILE);
$DExtractSqlSth->finish;

we have quite a few sensors, but the ones I am working on right now have
290K and 68K records.

Thanks,

Brett


|--------+----------------------->
|        |          Steve        |
|        |          Suehring     |
|        |          <snort@braing|
|        |          ia.org>      |
|        |                       |
|        |          11/06/2002   |
|        |          01:49 PM     |
|        |                       |
|--------+----------------------->
  
> -----------------------------------------------------------------------------------------------------------------------|
  |                                                                                                                     
  |
  |       To:     Brett.Gillett () tsx ca                                                                               
     |
  |       cc:     snort-users () lists sourceforge net                                                                  
     |
  |       Subject:     Re: [Snort-users] Snort Archive                                                                  
  |
  
> -----------------------------------------------------------------------------------------------------------------------|




Hello,

Can you send the SQL statements and/or the portions of the perl script
that you believe are failing?  Also, how big is the database?

Steve

On Wed, Nov 06, 2002 at 12:29:45PM -0500, Brett.Gillett () tsx ca wrote:
> Hi everyone,
>
> I have a question about archiving snort data.  Currently, we have our
> sensors logging
> to a local MySql database - what I would like to do is move the data to
an
> archive database
> and reduce the number of fields stored.  Has anyone done this before?  I
> have created
> some perl scripts to select data from each day and dump it into an
archive
> database, but
> it seems to be failing, and only selecting a subset of the data.  I am
not
> sure if it's the
> SQL query itself or the fact that the snort DB is already so huge.
>
> Has anyone done something similar?  If so, some pointers would be
> appreciated.
>
> Thanks,
>
> Brett
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by: See the NEW Palm
> Tungsten T handheld. Power & Color in a compact size!
> http://ads.sourceforge.net/cgi-bin/redirect.pl?palm0001en
> _______________________________________________
> Snort-users mailing list
> Snort-users () lists sourceforge net
> Go to this URL to change user options or unsubscribe:
> https://lists.sourceforge.net/lists/listinfo/snort-users
> Snort-users list archive:
> http://www.geocrawler.com/redir-sf.php3?list=snort-users






-------------------------------------------------------
This sf.net email is sponsored by: See the NEW Palm 
Tungsten T handheld. Power & Color in a compact size!
http://ads.sourceforge.net/cgi-bin/redirect.pl?palm0001en
_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users