Re: Snort Alert MySQL Query

[adonis okpidi <adonisokpidi () gmail com>]

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