Snort mailing list archives
Snort Alert MySQL Query
From: adonis okpidi <adonisokpidi () gmail com>
Date: Mon, 14 Mar 2016 23:06:19 +0000
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
------------------------------------------------------------------------------ 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!
Current thread:
- Snort Alert Mysql Query adonis okpidi (Feb 13)
- Re: Snort Alert Mysql Query wkitty42 (Feb 13)
- Re: Snort Alert Mysql Query adonis okpidi (Feb 14)
- Re: Snort Alert Mysql Query Rob MacGregor (Feb 15)
- Message not available
- Re: Snort Alert Mysql Query Rob MacGregor (Feb 15)
- Message not available
- Re: Snort Alert Mysql Query Rob MacGregor (Feb 15)
- Re: Snort Alert Mysql Query adonis okpidi (Feb 14)
- Re: Snort Alert Mysql Query wkitty42 (Feb 13)
- <Possible follow-ups>
- Snort Alert MySQL Query adonis okpidi (Mar 14)
- Re: Snort Alert MySQL Query 강명훈 (Mar 15)
- Re: Snort Alert MySQL Query adonis okpidi (Mar 15)
- Re: Snort Alert MySQL Query 강명훈 (Mar 15)