Snort Alert MySQL Query

[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

------------------------------------------------------------------------------
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!