Export ethereal cap file to SQL database with all details

[nksdata]

Hi All,

I want to export ethereal cap file to SQL database with all details.

Please suggest any method for it.

Regards,
Nagesh Lad

------------------------------------------------------------------------
Test Your IDS

Is your IDS deployed correctly?
Find out quickly and easily by testing it
with real-world attacks from CORE IMPACT.
Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708 
to learn more.
------------------------------------------------------------------------

[MARTIN Benoni]

Do you want to do this one or periodically ? For once, really easy :

1. Import your pcap file under Ethereal.
2. Export it in CSV format (http://www.ethereal.com/docs/eug_html/#ChIOExportCSVDialog)
3. Import the CSV file in a SQL Database. Drop me an email if you want the stored procedure for this (10 lines max :) )

Hope this helps !



-----Message d'origine-----
De : nksdata@... [mailto:nksdata@...]
Envoyé : jeudi 10 août 2006 11:20
À : focus-ids@...
Objet : Export ethereal cap file to SQL database with all details

Hi All,



I want to export ethereal cap file to SQL database with all details.



Please suggest any method for it.



Regards,

Nagesh Lad

------------------------------------------------------------------------
Test Your IDS

Is your IDS deployed correctly?
Find out quickly and easily by testing it with real-world attacks from CORE IMPACT.
Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708
to learn more.
------------------------------------------------------------------------


------------------------------------------------------------------------
Test Your IDS

Is your IDS deployed correctly?
Find out quickly and easily by testing it
with real-world attacks from CORE IMPACT.
Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708
to learn more.
------------------------------------------------------------------------

[Jason Brvenik]

I just cooked up a perl script to do just this because people keep
wanting to use Snort for it. The script uses a Snort database as it's
back end and will create log entries the same as the Snort engine would,
including packet data.

The code is still very much in the early phase but a functioning version
with minimal testing is available @

http://cerberus.sourcefire.com/~jbrvenik/unified_perl/

MARTIN Benoni wrote:

> Do you want to do this one or periodically ? For once, really easy :
>
> 1. Import your pcap file under Ethereal.
> 2. Export it in CSV format (http://www.ethereal.com/docs/eug_html/#ChIOExportCSVDialog)
> 3. Import the CSV file in a SQL Database. Drop me an email if you want the stored procedure for this (10 lines max :) )
>
> Hope this helps !
>
>
>
> -----Message d'origine-----
> De : nksdata@... [mailto:nksdata@...]
> Envoyé : jeudi 10 août 2006 11:20
> À : focus-ids@...
> Objet : Export ethereal cap file to SQL database with all details
>
> Hi All,
>
>
>
> I want to export ethereal cap file to SQL database with all details.
>
>
>
> Please suggest any method for it.
>
>
>
> Regards,
>
> Nagesh Lad
>
> ------------------------------------------------------------------------
> Test Your IDS
>
> Is your IDS deployed correctly?
> Find out quickly and easily by testing it with real-world attacks from CORE IMPACT.
> Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708
> to learn more.
> ------------------------------------------------------------------------
>
>
> ------------------------------------------------------------------------
> Test Your IDS
>
> Is your IDS deployed correctly?
> Find out quickly and easily by testing it
> with real-world attacks from CORE IMPACT.
> Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708 
> to learn more.
> ------------------------------------------------------------------------
>
>

--
Jason Brvenik - Sourcefire
PGP: 89C6 DE77 3B32 FC03 A5AE B5DD 11DF 4C8B 0D8E 3383
Key: http://cerberus.sourcefire.com/~jbrvenik/jason.brvenik.pgp.key

------------------------------------------------------------------------
Test Your IDS

Is your IDS deployed correctly?
Find out quickly and easily by testing it
with real-world attacks from CORE IMPACT.
Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708 
to learn more.
------------------------------------------------------------------------

[Hugo Francisco González Robledo]

Hi

i have a PoC on perl to send the results of TCPCUMP to MySQL, i know
it's not perfect, but it works for me.

regards

<perl code> -------------------------------

#!/usr/bin/perl

use DBI;


print "\n\n";
print "Programa para pasar los registros de tcpdump a mysql\n";
print "hfgr  Agosto 2005\n\n\n";


#preprando la conexión con el servidor MySQL
my $dns = "DBI:mysql:dumplog;localhost";
my $dbh = DBI->connect($dns,"root","");

my $src, my $dst;

#mientras existan registros ....
while (<STDIN>) {
    chomp($data = <STDIN>);
#    print $data . "\n";
    ($mdate, $timestamp, $proto,  $src,$si, $dst ) = split " ",$data;

    $src =~ s/\./-/g;

    @tp1 = split "-",$src;
    $srcc = "$tp1[0].$tp1[1].$tp1[2].$tp1[3]";
    $ps = $tp1[4];

    $dst =~ s/\./-/g;
    @tp1 = split "-",$dst;
    $dstt = "$tp1[0].$tp1[1].$tp1[2].$tp1[3]";
    ($pd) = split ":",$tp1[4];
#    print "$timestamp, $srcc, $ps , $dstt, $pd \n";
#    print ".";

    if($proto eq 'IP') {
    # Insertando los datos a la tabla
      my $sth = $dbh-> prepare("
      INSERT INTO tip VALUES
('$mdate','$timestamp','$srcc',$ps,'$dstt',$pd)" );
      $sth->execute;
    }
}

#Terminando la conexión con el servidor MySQL
$dbh->disconnect;

</perl code>

and i use like that :

# tcpdump -nn | tcp2my.pl

the sql code to mysql is :

<myslq code>

create database dumplog;
use dumplog;

create table tip (
 mdate varchar(12),
 timestamp varchar(24),
 hsrc varchar(15),
 psrc integer,
 hdst varchar(15),
 pdst integer
);

</mysql code>


On Thu, Aug 10, 2006 at 09:20:13AM -0000, nksdata@... wrote:

> Hi All,
>
> I want to export ethereal cap file to SQL database with all details.
>
> Please suggest any method for it.
>
> Regards,
> Nagesh Lad
>
> ------------------------------------------------------------------------
> Test Your IDS
>
> Is your IDS deployed correctly?
> Find out quickly and easily by testing it
> with real-world attacks from CORE IMPACT.
> Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708 
> to learn more.
> ------------------------------------------------------------------------

--
Hugo Francisco González Robledo
Instituto Tecnológico de San Luis Potosí

Llave pública en http://www.honeynet.org.mx
Llave pública en http://ardilla.zapto.org

Preguntale a Google-Earth donde estoy :
http://ardilla.zapto.org/ubicaHugo.kml

-------------------------------------------
Educación es lo que queda después de olvidar
lo que se ha aprendido en la escuela.
                Albert Einstein
-------------------------------------------

------------------------------------------------------------------------
Test Your IDS

Is your IDS deployed correctly?
Find out quickly and easily by testing it
with real-world attacks from CORE IMPACT.
Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708 
to learn more.
------------------------------------------------------------------------

[Raffael Marty]

Well, AfterGlow (afterglow.sourceforge.net) has a tcpdump parser which
is fairly good... (beware, I am biased ;)

It takes tcpdump and geneates csv output:

tcpdump -vttttnneli ath0 | ./tcpdump2csv.pl "sip dip dport"

This will for example generate output with just the sourceIP, the
destinationIP and the destinationPort. Check the source to see other
fields that the parser understands...

The nice thing is also that it takes care of the client->server pairs.
Meaning that for the responses, the source and destionation is inverted.

Hope this helps...

  -raffy

> Hi
>
> i have a PoC on perl to send the results of TCPCUMP to MySQL, i know
> it's not perfect, but it works for me.
>
> regards
>
> <perl code> -------------------------------
>
> #!/usr/bin/perl
>
> use DBI;
>
>
> print "\n\n";
> print "Programa para pasar los registros de tcpdump a mysql\n";
> print "hfgr  Agosto 2005\n\n\n";
>
>
> #preprando la conexión con el servidor MySQL
> my $dns = "DBI:mysql:dumplog;localhost";
> my $dbh = DBI->connect($dns,"root","");
>
> my $src, my $dst;
>
> #mientras existan registros ....
> while (<STDIN>) {
>     chomp($data = <STDIN>);
> #    print $data . "\n";
>     ($mdate, $timestamp, $proto,  $src,$si, $dst ) = split " ",$data;
>
>     $src =~ s/\./-/g;
>
>     @tp1 = split "-",$src;
>     $srcc = "$tp1[0].$tp1[1].$tp1[2].$tp1[3]";
>     $ps = $tp1[4];
>
>     $dst =~ s/\./-/g;
>     @tp1 = split "-",$dst;
>     $dstt = "$tp1[0].$tp1[1].$tp1[2].$tp1[3]";
>     ($pd) = split ":",$tp1[4];
> #    print "$timestamp, $srcc, $ps , $dstt, $pd \n";
> #    print ".";
>
>     if($proto eq 'IP') {
>     # Insertando los datos a la tabla
>       my $sth = $dbh-> prepare("
>       INSERT INTO tip VALUES
> ('$mdate','$timestamp','$srcc',$ps,'$dstt',$pd)" );
>       $sth->execute;
>     }
> }
>
> #Terminando la conexión con el servidor MySQL
> $dbh->disconnect;
>
> </perl code>
>
> and i use like that :
>
> # tcpdump -nn | tcp2my.pl
>
> the sql code to mysql is :
>
> <myslq code>
>
> create database dumplog;
> use dumplog;
>
> create table tip (
>  mdate varchar(12),
>  timestamp varchar(24),
>  hsrc varchar(15),
>  psrc integer,
>  hdst varchar(15),
>  pdst integer
> );
>
> </mysql code>
>
>
> On Thu, Aug 10, 2006 at 09:20:13AM -0000, nksdata@... wrote:
> > Hi All,
> >
> > I want to export ethereal cap file to SQL database with all details.
> >
> > Please suggest any method for it.
> >
> > Regards,
> > Nagesh Lad
> >
> > ------------------------------------------------------------------------
> > Test Your IDS
> >
> > Is your IDS deployed correctly?
> > Find out quickly and easily by testing it
> > with real-world attacks from CORE IMPACT.
> > Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708 
> > to learn more.
> > ------------------------------------------------------------------------
>
> --
> Hugo Francisco González Robledo
> Instituto Tecnológico de San Luis Potosí
>
> Llave pública en http://www.honeynet.org.mx
> Llave pública en http://ardilla.zapto.org
>
> Preguntale a Google-Earth donde estoy :
> http://ardilla.zapto.org/ubicaHugo.kml
>
> -------------------------------------------
> Educación es lo que queda después de olvidar
> lo que se ha aprendido en la escuela.
> Albert Einstein
> -------------------------------------------
>
> ------------------------------------------------------------------------
> Test Your IDS
>
> Is your IDS deployed correctly?
> Find out quickly and easily by testing it
> with real-world attacks from CORE IMPACT.
> Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708 
> to learn more.
> ------------------------------------------------------------------------
>

--

Raffael Marty, GCIA, CISSP                    raffael.marty@...
Manager                                  Strategic Application Solutions
ArcSight, Inc.                                         [phone]

------------------------------------------------------------------------
Test Your IDS

Is your IDS deployed correctly?
Find out quickly and easily by testing it
with real-world attacks from CORE IMPACT.
Go to http://www.securityfocus.com/sponsor/CoreSecurity_focus-ids_040708
to learn more.
------------------------------------------------------------------------

[MrD]

Hi Raffy

> Well, AfterGlow (afterglow.sourceforge.net) has a tcpdump parser which
> is fairly good... (beware, I am biased ;)

I D/L'd afterglow v 1.5.6 from sf.net, I cannot find 2.0 for the java code
release. Where might I find it?.

There is only the perl in the D/L I got

Thanks Raffy