Thursday, June 15, 2006

Tracking your most active network protocols with Sguil

All you Sguil users out there might find this interesting. I wanted to figure out a way to track the top most active protocols flowing over my perimeter, where "active" in this case means "most bytes transferred either in or out of my network". This is actually a fairly simple database query, but I had a special requirement.

You see, I wanted to know not only the total number of bytes transferred, but I wanted to see it broken down by whether the traffic was entering my network or leaving my network. SANCP (Sguil's session collector) tracks bytes by source and destination, but that's not good enough. The source of one connection could be an Internet host, while the source of another connection could be a system on my own LAN. Just because SANCP records how many bytes the source host sent doesn't mean it knows whether they were entering or leaving my LAN.

Here's a SQL query that really can tell the difference:

select dst_port,
sum(to_mynet) as bytes_to_mynet,
sum(from_mynet) as bytes_from_mynet,
sum(to_mynet + from_mynet) as total_bytes
select dst_port,
src_bytes as from_mynet,
dst_bytes as to_mynet
from sancp where
(start_time between DATE_SUB(CURDATE(), INTERVAL 1 HOUR)
and CURDATE())
and (src_ip between INET_ATON("") and
INET_ATON("")) and
not (dst_ip between INET_ATON("") and
select dst_port,
dst_bytes as from_mynet,
src_bytes as to_mynet
from sancp where
(start_time between DATE_SUB(CURDATE(), INTERVAL 1 HOUR)
and CURDATE())
and not (src_ip between INET_ATON("") and
INET_ATON("")) and
(dst_ip between INET_ATON("") and
) as test_table
group by dst_port
order by total_bytes desc
limit 5;

The trick here is to do two queries, each limiting itself to either sources on the Internet or sources on my LAN. I can then use the "SELECT" statements to order the columns to reflect the direction of the data flow. In this query, the second column will always be the number of bytes flowing out of my network, and the third column will always be the number of bytes coming into my network. Of course, the first column is the destination port number, which corresponds roughly to the network protocol used.

These two queries are joined by a "UNION ALL" statement to make them into one large result set. The SQL code above treats this as a temporary table, from which the outer SELECT statement grabs it's data and does the final work of summing both incoming and outgoing data flows and creating a neat table.

The output of this query will look something like the following table. Note that I ran it against some very odd sample data, so it doesn't show a full 10 rows. Also, I messed with the numbers because I was uneasy about posting my real data here. Still, you can see what the report looks like.

| dst_port | bytes_to_mynet| bytes_from_mynet| total_bytes |
| 80 | 1816 | 51101804400 | 51101806216 |
| 22 | 1816 | 51101412328 | 51101414144 |
| 443 | 1816 | 51096413496 | 51096415312 |
| 53 | 1804400 | 18093887 | 18274327 |

To use this in your own network, simply change the IP addresses above to represent the beginning and ending of your own address space. As written, the query will track only a single hour of data (from 23:00 to 00:00 of the current day), but you can play with the time specification to get other reporting periods.


JimmytheGeek said...

This is AWESOME. This is exactly what my boss was asking for when MRTG showed weird traffic.

I pretty much presumed this was bittorrent traffic to my workstation (gotta love those vmware appliances) but it was painful to prove.

DavidJBianco said...

Thanks. I have to admit that I learned a lot about SQL while writing this query. I think the trick of selecting from the output of previous selects (I believe this is formally known as a "subselect") will be very useful in the future.

Donal said...

Maybe you have other needs, however:

Why don't you just enable (net)flows on your router/gateway?


DavidJBianco said...

There's no doubt that Netflow is a great way to collect network session data. I'm not using it, though, because Sguil integrates with SANCP but not with Netflow. It's essentially the same type of data, and I'm sure integration wouldn't be that hard, but I'm lazy.

Also, parts of my infrastructure aren't Netflow-capable, so I'd still have to do something else there. I used to use ntop to collect some similar information, and of course Netflow. If you're not using a Sguil NSM setup already, Netflow has a lot to recommend it.

Victor Julien said...

This query requires MySQL 4.1+ because 4.0 doesn't have the date functions. I updated and now it finally works. It rocks!