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.
