Re: Database formats

["Imran William Smith" <iwsmith () mimos my>]

Answer to question 2 only)

In moderately recent versions of MySQL,

select inet_ntoa(ip_src)


So, select ip_hdr.inet_ntoa(ip_src), count(*)
from 
    event
left outer join ip_hdr on (ip_hdr.sid = event.sid and ip_hdr.cid = event.cid)
where
    event.cid = 1
and
    other where clause as appropriate


There's also the opposite, inet_aton("a.b.c.d") -> integer.

I remember them as "ascii to number" and "number to ascii".  Maybe they're not the
correct names.  But it helps!

Note 1, some other RDBMS's don't have these conversion functions.  But with MySQL,
you are fine.

Note 2, any time you move from the event table to any other of the header tables,
you have to do an outer join, since the other header tables are optional.  For lots
of detailed SQL examples, get a copy of Acid, use the search function, and turn on
the debugging so you can see the SQL it generates.



--
Imran William Smith
Security Products Development
Mimos Bhd, Malaysia




  ----- Original Message ----- 
  From: Greg Robinson 
  To: snort-users () lists sourceforge net 
  Sent: Wednesday, July 17, 2002 9:23 AM
  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