[Snort-sigs] Snort Alert MySQL Query

adonis okpidi adonisokpidi at ...2420...
Mon Mar 14 19:06:19 EDT 2016


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.snort.org/pipermail/snort-sigs/attachments/20160314/c8cf10cb/attachment.html>


More information about the Snort-sigs mailing list