Re: Database formats

["Ian Macdonald" <secsnort () dirk demon co uk>]

Here is something I ran today, hope it helps

#POP3 single ip
select distinct CONCAT(CONV(substring(HEX(a.ip_dst),1,2),16,10),".",CONV(substring(HEX(a.ip_dst),3,2),16,10),
".", CONV(substring(HEX(a.ip_dst),5,2),16,10),".",CONV(substring(HEX(a.ip_dst),7,2),16,10)) as IP_DST
from iphdr a, event b, signature c where a.cid= b.cid
and a.sid=b.sid and c.sig_id = b.signature and c.sig_name like '%pop3%' and b.timestamp > DATE_SUB(Now(), INTERVAL 2 
DAY);


Change 'pop3' to what ever you are interested in, it is matching against signature name
You can change how long back the data goes by changing INTERVAL 2 DAY


Ian

  ----- Original Message ----- 
  From: Greg Robinson 
  To: snort-users () lists sourceforge net 
  Sent: Tuesday, July 16, 2002 9:23 PM
  Subject: [Snort-users] Database formats


  I am logging my snort server to a MySql Database....
  I have two questions..??
  1.  How do I get snort to only write to the database..and not the /var/log/snort directory also..??
  2.  How would I go about getting custom reports out of the snort database..??
      For instance: If i look at the iphdr table: I get the following output..??
      mysql> select * from iphdr where cid = '1';   
  
+-----+-----+------------+------------+--------+---------+--------+--------+-------+----------+--------+--------+----------+---------+
  | sid | cid | ip_src     | ip_dst     | ip_ver | ip_hlen | ip_tos | ip_len | ip_id | ip_flags | ip_off | ip_ttl | 
ip_proto | ip_csum |
  
+-----+-----+------------+------------+--------+---------+--------+--------+-------+----------+--------+--------+----------+---------+
  |   1 |   1 | 1065291291 | 3487996171 |      4 |       5 |      0 |    753 | 16405 |        0 |      0 |    113 |     
   6 |    4198 |
  |   2 |   1 | 1036618565 | 3487996171 |      4 |       5 |     16 |    623 |     0 |        0 |      0 |    240 |     
   6 |       0 |
  
+-----+-----+------------+------------+--------+---------+--------+--------+-------+----------+--------+--------+----------+---------+
  2 rows in set (1.77 sec)
  How do I convert the ip_src field back to an IP address so I could write a select statement to find out how many 
times that ip_src is in the database..??

  Thanks in advance...

  Greg