Sguil: creating custom reports
In the Sguil NSM system, Sancp plays a vital role. Sancp records session data, in which all connections are recorded. For all connections (and pseudo connections, think udp, icmp), Sancp records the number of bytes transfered, number of packets, start and end time, etc. This is very much useful information, of which Sguil only makes a subset accessable. Because the information is stored in a MySQL database, nothing prevents you from querying the database manually, which is what i did. However, David Bianco suggested that i could also add them as ‘canned reports’ to Sguil, which i did.
At this stage i am mostly interested in the information from Sancp about the traffic volume. Which host(s) use the most bandwidth? Questions like this. Below i explain one of the ‘canned reports’ i created.
This is the output of the query below. What it shows is that 192.168.1.2 in my lan downloaded 367MB from 145.97.193.148, and that it did this via http (protocol 6 is tcp, port is 80). Let’s have a look at the query.
TOPTENWAN2LAN_SRC_DST_SER||Top b/w per serv. from WAN to LAN (downloads), flow from WAN-IPs to LAN-IPs, volume in MB||query||select sum(dst_bytes)/1048576 as my_dst_bytes, INET_NTOA(dst_ip), INET_NTOA(src_ip), ip_proto, dst_port from sancp INNER JOIN sensor ON sancp.sid=sensor.sid WHERE start_time > %%STARTTIME%% AND end_time 0 AND %%SENSORS%% and ((src_ip between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) or (src_ip between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) or (src_ip between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) and ((dst_ip not between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) and (dst_ip not between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) and (dst_ip not between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) GROUP BY src_ip,dst_ip,ip_proto,dst_port ORDER BY my_dst_bytes DESC LIMIT 10||5||
Copy-paste this into your /etc/sguild/sguild.reports and restart sguild. It should be available in the Sensor Reports window. The parts between percent signs are Sguil variables. The double pipes are separators between different sections of the line. The line starts with a sort of internal name for Sguil. What follows is the description as it will be show in the report selection screen and in the report itself. Next it is indicated that his is a query. After that the query itself. After the query in the last field it is indicated how many columns of data Sguil can expect from MySQL.
Now the query. First, i select dst_bytes, dst_ip and src_ip, ip_proto and dst_port. The dst_bytes field contains the number of bytes flowing from dst_ip to src_ip. Because we use group later, we SUM dst_bytes. And because it is in bytes, we divide it by 1024*1024(=1048576) so the result will be megabytes. The result is stored in my_dst_bytes. We then make sure that the src_ip is in a private ip range and that dst_ip is not in a private ip-range. Then the grouping is done, followed by the ordering by my_dst_bytes. Easy huh!
Naturally, this only works for networks that actually use private ip-ranges, but it should be easy to adapt if you use something else. Below, i have added another seven of these reports.
Below are slight modified versions of the same query. The first leaves out the service information. The second the specific WAN IP addresses. The third shows just the LAN IP and the volume.
TOPTENWAN2LAN_SRC_DST||Top b/w from WAN to LAN (downloads), flow from WAN-IPs to LAN-IPs, volume in MB||query||select sum(dst_bytes)/1048576 as my_dst_bytes, INET_NTOA(dst_ip), INET_NTOA(src_ip) from sancp INNER JOIN sensor ON sancp.sid=sensor.sid WHERE start_time > %%STARTTIME%% AND end_time 0 AND %%SENSORS%% and ((src_ip between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) or (src_ip between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) or (src_ip between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) and ((dst_ip not between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) and (dst_ip not between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) and (dst_ip not between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) GROUP BY src_ip,dst_ip ORDER BY my_dst_bytes DESC LIMIT 10||3||
TOPTENWAN2LAN_SRC_SER||Top b/w per serv. from WAN to LAN (downloads), flow from WAN to LAN-IPs, volume in MB||query||select sum(dst_bytes)/1048576 as my_dst_bytes, INET_NTOA(src_ip), ip_proto, dst_port from sancp INNER JOIN sensor ON sancp.sid=sensor.sid WHERE start_time > %%STARTTIME%% AND end_time 0 AND %%SENSORS%% and ((src_ip between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) or (src_ip between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) or (src_ip between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) and ((dst_ip not between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) and (dst_ip not between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) and (dst_ip not between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) GROUP BY src_ip,ip_proto,dst_port ORDER BY my_dst_bytes DESC LIMIT 10||4||
TOPTENWAN2LAN_SRC||Top b/w from WAN to LAN (downloads), flow from WAN to LAN-IPs, volume in MB||query||select sum(dst_bytes)/1048576 as my_dst_bytes, INET_NTOA(src_ip) from sancp INNER JOIN sensor ON sancp.sid=sensor.sid WHERE start_time > %%STARTTIME%% AND end_time 0 AND %%SENSORS%% and ((src_ip between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) or (src_ip between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) or (src_ip between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) and ((dst_ip not between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) and (dst_ip not between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) and (dst_ip not between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) GROUP BY src_ip ORDER BY my_dst_bytes DESC LIMIT 10||2||
Next are the same four queries, but this time for traffic going out of the LAN.
TOPTENLAN2WAN_SRC_DST_SER||Top b/w per serv. from LAN to WAN (uploads/locally hosted server visits), flow from LAN-IPs to WAN-IPs, volume in MB||query||select sum(dst_bytes)/1048576 as my_dst_bytes, INET_NTOA(dst_ip), INET_NTOA(src_ip), ip_proto, dst_port from sancp INNER JOIN sensor ON sancp.sid=sensor.sid WHERE start_time > %%STARTTIME%% AND end_time 0 AND %%SENSORS%% and ((dst_ip between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) or (dst_ip between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) or (dst_ip between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) and ((src_ip not between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) and (src_ip not between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) and (src_ip not between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) GROUP BY src_ip,dst_ip,ip_proto,dst_port ORDER BY my_dst_bytes DESC LIMIT 10||5||
TOPTENLAN2WAN_SRC_DST||Top b/w from LAN to WAN (uploads/locally hosted server visits), flow from LAN-IPs to WAN-IPs, volume in MB||query||select sum(dst_bytes)/1048576 as my_dst_bytes, INET_NTOA(dst_ip), INET_NTOA(src_ip) from sancp INNER JOIN sensor ON sancp.sid=sensor.sid WHERE start_time > %%STARTTIME%% AND end_time 0 AND %%SENSORS%% and ((dst_ip between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) or (dst_ip between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) or (dst_ip between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) and ((src_ip not between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) and (src_ip not between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) and (src_ip not between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) GROUP BY src_ip,dst_ip ORDER BY my_dst_bytes DESC LIMIT 10||3||
TOPTENLAN2WAN_SRC_SER||Top b/w per serv. from LAN to WAN (uploads/locally hosted server visits), flow from LAN to WAN-IPs, volume in MB||query||select sum(dst_bytes)/1048576 as my_dst_bytes, INET_NTOA(src_ip), ip_proto, dst_port from sancp INNER JOIN sensor ON sancp.sid=sensor.sid WHERE start_time > %%STARTTIME%% AND end_time 0 AND %%SENSORS%% and ((dst_ip between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) or (dst_ip between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) or (dst_ip between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) and ((src_ip not between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) and (src_ip not between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) and (src_ip not between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) GROUP BY src_ip,ip_proto,dst_port ORDER BY my_dst_bytes DESC LIMIT 10||4||
TOPTENLAN2WAN_SRC||Top b/w from LAN to WAN (uploads/locally hosted server visits), flow from LAN to WAN-IPs, volume in MB||query||select sum(dst_bytes)/1048576 as my_dst_bytes, INET_NTOA(src_ip) from sancp INNER JOIN sensor ON sancp.sid=sensor.sid WHERE start_time > %%STARTTIME%% AND end_time 0 AND %%SENSORS%% and ((dst_ip between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) or (dst_ip between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) or (dst_ip between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) and ((src_ip not between INET_ATON(“192.168.0.0”) and INET_ATON(“192.168.255.255”)) and (src_ip not between INET_ATON(“10.0.0.0”) and INET_ATON(“10.255.255.255”)) and (src_ip not between INET_ATON(“172.16.0.0”) and INET_ATON(“172.31.255.255”))) GROUP BY src_ip ORDER BY my_dst_bytes DESC LIMIT 10||2||
Enjoy!
Updated: the table from the report view looked horrible, so i replaced it with a screenshot.