View source for Reporting and Data Mining

One of the nice things about Sguil is that most of the information (IDS alerts and network session data) are in the MySQL database. This gives you a great opportunity to do some very cool Data Mining via SQL queries.  
== Sguil Reporting ==
If you're looking for a reporting solution for Sguil, complete with pretty charts and graphs, check out one of the following:
* [http://www.squertproject.org/ SQueRT]
* [[Sguil Reports with BIRT HOWTO]].
== SQL Queries ==
In this section, you'll find SQL queries that you can run against your sguil database to uncover additional information about what's going on with your network.  Many of these would be great for nightly reports, for example.
Please feel free to add your own queries to this list, just be sure to give a one-sentence (or so) explanation of what your query is looking for, so readers can tell whether or not it's something they're interested in.
; [[Query for reset-bit|Top sources of TCP RST packets]] : Good for detecting systems that may be trying to scan your network
== Useful Sensor/Bandwidth connection reports ==
 |BNz, provided these in the IRC channel, pretty  cool... :) You need to restart sguild, and close/open
 the client to see the updated report options..
 Last 10 minutes Top 20 proto/dst_port combinations ordered by total src_bytes
 Last 10 minutes Top 20 proto/dst_port combinations ordered by total dst_bytes
 Last 10 minutes Top 20 proto/dst_port combinations ordered by connections
 Last 10 minutes Top 20 destination IP addresses ordered by connections
 Last 10 minutes Top 20 destination IP addresses ordered by total src_bytes
 Last 10 minutes Top 20 destination IP addresses ordered by total dst_bytes
 Last 10 minutes Top 20 source IP addresses ordered by connections
 Last 10 minutes Top 20 source IP addresses ordered by total src_bytes
 Last 10 minutes Top 20 source IP addresses ordered by total dst_bytes
To install these reports, append the below file excerpt to sguil/server/sguild.reports:
<pre>
REPORT1||Last 10 minutes Top 20 proto/dst_port combinations ordered by total src_bytes||query||select count(sancp.sancpid) as \
no_conns,sensor.hostname,sancp.dst_port,sancp.ip_proto,sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as \
total_dst_bytes from sancp,sensor where sensor.sid=sancp.sid and sancp.start_time>now()-interval 10 minute group by \
sancp.sid,sancp.dst_port,sancp.ip_proto order by total_src_bytes desc limit 0,20||6||
REPORT2||Last 10 minutes Top 20 proto/dst_port combinations ordered by total dst_bytes||query||select count(sancp.sancpid) as \
no_conns,sensor.hostname,sancp.dst_port,sancp.ip_proto,sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as total_dst_bytes \
from sancp,sensor where sensor.sid=sancp.sid and sancp.start_time>now()-interval 10 minute group by \
sancp.sid,sancp.dst_port,sancp.ip_proto order by total_dst_bytes desc limit 0,20||6||
REPORT3||Last 10 minutes Top 20 proto/dst_port combinations ordered by connections||query||select count(sancp.sancpid) as \
no_conns,sensor.hostname,sancp.dst_port,sancp.ip_proto,sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as total_dst_bytes \
from sancp,sensor where sensor.sid=sancp.sid and sancp.start_time>now()-interval 10 minute group by \
sancp.sid,sancp.dst_port,sancp.ip_proto order by no_conns desc limit 0,20||6||
REPORT4||Last 10 minutes Top 20 destination IP addresses ordered by connections||query||select count(sancp.sancpid) as \
no_conns,sensor.hostname,inet_ntoa(sancp.dst_ip),sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as total_dst_bytes from \
sancp,sensor where sensor.sid=sancp.sid and sancp.start_time>now()-interval 10 minute group by sancp.sid,sancp.dst_ip order by no_conns \
desc limit 0,20||6||
REPORT5||Last 10 minutes Top 20 destination IP addresses ordered by total src_bytes||query||select count(sancp.sancpid) as \
no_conns,sensor.hostname,inet_ntoa(sancp.dst_ip),sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as total_dst_bytes from \
sancp,sensor where sensor.sid=sancp.sid and sancp.start_time>now()-interval 10 minute group by sancp.sid,sancp.dst_ip order by \
total_src_bytes desc limit 0,20||6||
REPORT6||Last 10 minutes Top 20 destination IP addresses ordered by total dst_bytes||query||select count(sancp.sancpid) as \
no_conns,sensor.hostname,inet_ntoa(sancp.dst_ip),sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as total_dst_bytes from \
sancp,sensor where sensor.sid=sancp.sid and sancp.start_time>now()-interval 10 minute group by sancp.sid,sancp.dst_ip order by  \
total_dst_bytes desc limit 0,20||6||
REPORT7||Last 10 minutes Top 20 source IP addresses ordered by connections||query||select count(sancp.sancpid) as  \
no_conns,sensor.hostname,inet_ntoa(sancp.src_ip),sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as total_dst_bytes from \
sancp,sensor where sensor.sid=sancp.sid and sancp.start_time>now()-interval 10 minute group by sancp.sid,sancp.src_ip order by no_conns \
desc limit 0,20||6||
REPORT8||Last 10 minutes Top 20 source IP addresses ordered by total src_bytes||query||select count(sancp.sancpid) as \
no_conns,sensor.hostname,inet_ntoa(sancp.src_ip),sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as total_dst_bytes from \
sancp,sensor where sensor.sid=sancp.sid and sancp.start_time>now()-interval 10 minute group by sancp.sid,sancp.src_ip order by  \
total_src_bytes desc limit 0,20||6||
REPORT9||Last 10 minutes Top 20 source IP addresses ordered by total dst_bytes||query||select count(sancp.sancpid) as \
no_conns,sensor.hostname,inet_ntoa(sancp.src_ip),sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as total_dst_bytes from \
sancp,sensor where sensor.sid=sancp.sid and sancp.start_time>now()-interval 10 minute group by sancp.sid,sancp.src_ip order by   \
total_dst_bytes desc limit 0,20||6||
</pre>
The above reports automatically run against data from all sensors, going back 10 minutes from the current time. Changing the sensor and time range selections in your Sguil client will have no effect. If you want to choose the sensors and time range when running the reports from the Sguil client, you can use these reports with the %%SENSORS%%, %%STARTTIME%%, and %%ENDTIME%% macros. Here is an example using REPORT9 from above:
<pre>
REPORT9||Top 20 source IP addresses ordered by total dst_bytes||query||select count(sancp.sancpid) as \
no_conns,sensor.hostname,inet_ntoa(sancp.src_ip),sum(sancp.src_bytes) as total_src_bytes,sum(sancp.dst_bytes) as total_dst_bytes from \
sancp,sensor where sensor.sid=sancp.sid and %%SENSORS%% and sancp.start_time > %%STARTTIME%% and sancp.start_time < %%ENDTIME%% \
group by sancp.sid,sancp.src_ip order by total_dst_bytes desc limit 0,20||6||
</pre>