[Michael Cohen <michael.cohen@xxxxxxxxxxxxxxx>]

Hi Everyone,
  I have recently developed a patch for ethereal to produce SQL statements 
from the protocol dissectors. This was done as part of an application 
developed for my employer but the patch is (obviously) available under the 
terms of the GPL. The patch adds a number of extra command line parameters to 
ethereal:

This produces an SQL schema generated by the registered dissectors
ethereal -G sql

create table dns_data (key_id int, name varchar(100), type varchar(10), class 
varchar(10),data text
,key key_id(key_id)); create table data (key_id int, length int, data blob,key 
key_id(key_id));
create table enum (name varchar(50),value int,string varchar(50));
insert into meta set property="tcpdump_table", value= "dns";
Create table `dns` (
/* Length : Length of DNS-over-TCP request or response */
dns_length SMALLINT UNSIGNED,
/* Flags :  */
dns_flags SMALLINT UNSIGNED,
/* Transaction ID : Identification of transaction */
dns_id SMALLINT UNSIGNED,
/* Questions : Number of queries in packet */
dns_count_queries SMALLINT UNSIGNED,
/* Answer RRs : Number of answers in packet */
dns_count_answers SMALLINT UNSIGNED,
/* Authority RRs : Number of authoritative records in packet */
dns_count_auth_rr SMALLINT UNSIGNED,
/* Additional RRs : Number of additional records in packet */
dns_count_add_rr SMALLINT UNSIGNED,
key_id INT );

etc etc.. (Note that the schema is generated from the types registered for 
each dissector so the database types are efficiently chosen).

Then one can call tethereal with the Q switch to generate actual SQL:
tethereal -VxQr somedumpfile.dump

INSERT into `frame` set
frame_time = "2001/9/30 19:15:41",
frame_pkt_len = "60",
frame_cap_len = "60",
key_id = 1;
INSERT into `eth` set
eth_dst = "08:00:20:93:d6:79",
eth_src = "00:a0:c9:1a:5f:1b",
eth_type = "2048",
key_id = 1;
INSERT into `ip` set
ip_hdr_len = "20",
ip_len = "40",
ip_id = "63829",
ip_flags = "4",
ip_frag_offset = "0",
ip_ttl = "128",
ip_proto = "6",
ip_checksum = "55236",
ip_src = "168430244",
ip_dst = "168430334",
key_id = 1;

etc etc... (Again note that the results are well typed, for example IP 
addresses are 32bit ints rather than strings etc). 

This result can be piped directly into the mysql client for insertion into the 
database (should also work with other databases but I havent tried).

One can then use this information to query the database and even do stream 
reassembly _extremely_ quickly (the application this is used in reassembles 
web pages from the dump, piccies and all with very good response times even 
for large dumps). It is then possible to query the database for statistics 
and other interesting trends in the data externally from ethereal, in an off 
line batch format. Particularly exciting is the ability to dissect complex 
protocols and collect statistics from within the database (e.g. SMB network 
discovery etc).

I am wondering if there is any interest in incorporating the patch into the 
main stream distribution initially and any guidelines as to how to go about 
doing that. (in terms of code cleanups, testing etc).

Due to  the size of the patch (about 52kb) please email me directly for a copy 
if anyone wants to have a play.

Michael.