Re: Snort Alert MySQL Query

[강명훈 <mhkang589 () gmail com>]

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 () gmail com>:

> 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

------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785231&iu=/4140

_______________________________________________
Snort-sigs mailing list
Snort-sigs () lists sourceforge net
https://lists.sourceforge.net/lists/listinfo/snort-sigs
http://www.snort.org


Please visit http://blog.snort.org for the latest news about Snort!