[Snort-sigs] Snort Alert MySQL Query

adonis okpidi adonisokpidi at ...2420...
Tue Mar 15 10:08:42 EDT 2016


Hello, 

Thanks for the reply :) I was wondering why you had to set a fixed date? My data currently has only 2003 alerts 2010.
What I want is Show the first time of the first occurrence of the time for a source IP and count the other things in the other columns to show how many similar source ports it attacked, distinct destination IP address attacked, distinct destination port attacked, distinct protocol used, distinct signature_name, distinct signature_class_name. All for that one source IP 

Thanks again for the reply I truly appreciate 

Best Regards,
Adonis Okpidi

> On 15 Mar 2016, at 06:02, 강명훈 <mhkang589 at ...3422.....> wrote:
> 
> Hi,
> 
> The result of 'count(c.ip_dst)' and 'count(distinct c.ip_dst)' ) are not same.
> Which result do you want?
> And i think the realtime condition is not good condition for statistics.
> I think need to scale down time scope.
> Have a nice day~
> 
> select date(a.timestamp), inet_ntoa(c.ip_src), count(distinct c.ip_dst), count(distinct b.sig_name), count(a.signature)
> from event a, signature b, iphdr c
> where a.signature = b.sig_id 
> and a.sid = c.sid and a.cid = c.cid 
> and date_format(a.timestamp, '%Y-%m') = '2016-01'
> group by date(a.timestamp), inet_ntoa(c.ip_src)
> 
> select date_format(a.timestamp, '%H:%i'), inet_ntoa(c.ip_src), count(distinct c.ip_dst), count(distinct b.sig_name), count(a.signature)
> from event a, signature b, iphdr c
> where a.signature = b.sig_id 
> and a.sid = c.sid and a.cid = c.cid 
> and date(a.timestamp) = '2016-01-07'
> group by date_format(a.timestamp, '%H:%i'), inet_ntoa(c.ip_src)
> 
> Best Regards
> 
> 2016-03-15 8:06 GMT+09:00 adonis okpidi <adonisokpidi at ...2420...>:
>> Hi,
>> 
>> I am attempting to write a query that would return all distinct source ip and count the number of unique values of the columns shown in the query below. Here is an example of the output I want
>> 
>> 
>> # Sensor, Date_Time, SrcIP, SPort, DstIP, DPort, Protocol, Signature_name, Signature_class_name, num of occurence
>> 
>> '2', '2003-09-04 19:54:27', '192.168.0.1', '80', '24', '25', '17', '4', '5', '24'
>> 
>> I used Barnyard2 to read the snort.log file into MySQL database 
>> 
>> select count(f.hostname) as Sensor, a.timestamp as Date_Time, inet_ntoa(d.ip_src) as SrcIP, count(c.tcp_sport) as SPort, count(inet_ntoa(d.ip_dst)) as DstIP, count(c.tcp_dport) as DPort, count(d.ip_proto) as Protocol, count(b.sig_name) as Signature_name, count(e.sig_class_name) as Signature_class_name, count(a.signature) as num
>> from event a, signature b, tcphdr c, iphdr d, sig_class e, sensor f 
>> where a.signature = b.sig_id 
>> and a.sid = c.sid and a.cid = c.cid 
>> and a.sid = d.sid and a.cid = d.cid 
>> and a.signature = b.sig_id 
>> and b.sig_class_id = e.sig_class_id 
>> and a.sid = f.sid 
>> group by inet_ntoa(d.ip_src), inet_ntoa(d.ip_src), c.tcp_dport
>> union
>> select count(f.hostname) as Sensor, a.timestamp as Date_Time, inet_ntoa(d.ip_src) as SrcIP, count(c.udp_sport) as SPort, count(inet_ntoa(d.ip_dst)) as DstIP, count(c.udp_dport) as DPort, count(d.ip_proto) as Protocol, count(b.sig_name) as Signature_name, count(e.sig_class_name) as Signature_class_name, count(a.signature) as num
>> from event a, signature b, udphdr c, iphdr d, sig_class e, sensor f 
>> where a.signature = b.sig_id 
>> and a.sid = c.sid and a.cid = c.cid 
>> and a.sid = d.sid and a.cid = d.cid 
>> and a.signature = b.sig_id 
>> and b.sig_class_id = e.sig_class_id 
>> and a.sid = f.sid 
>> group by inet_ntoa(d.ip_src), c.udp_sport, udp_dport
>> order by Date_Time desc;
>> 
>> Best Regards, 
>> Adonis Okpidi
> 
> 
> 
> -- 
> -----------------------
> Kang Myoung-hun
> -----------------------
> +82-10 6604 6084
> kangmyounghun.blogspot.kr
> kr.linkedin.com/pub/myounghun-kang/74/238/93a
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.snort.org/pipermail/snort-sigs/attachments/20160315/d0489d8d/attachment.html>


More information about the Snort-sigs mailing list