Re: Database formats

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

Or on MySQL, use the much simpler

inet_ntoa() function

which will also convert a single 32bit-int format IP address into a dotted-decimal
IP address.

This complex method might be necessary where you don't have the inet_ntoa()
or its equivalent (e.g. Oracle).  For Oracle, you have to replace concat with || , etc.


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



  ----- Original Message ----- 
  From: Ian Macdonald 
  To: Greg Robinson ; snort-users () lists sourceforge net 
  Sent: Wednesday, July 24, 2002 4:41 AM
  Subject: Re: [Snort-users] Database formats


  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