Made my own Netflow Collector…
-
Just thought I’d share some details on a little project I have been working on
I have been looking for at way to get reliable usage statistics for hosts / interfaces / protocols and so forth on my Local LAN. Ususally this is best done with a netflow collector/analyser, but those are either VERY expensive, or QUITE ressource demanding and complex.
Alternatively, some of it can be done with a pfSense package like TrafficTotals, Darkstat or BandwidthD. But those do stats based on packetdumps, thus requiring CPU and ressources on the firewall. Its like using a canon to shoot birds.
So I decided to create my own little Netflow collector that is capable of running on a small Raspberry Pi or similar if it has an SSD to prevent it from exausting the write endurance of its storage right away.
I’m using the native flow export feature of pfSense+ as that is HIGHLY effective. It’s a native feature of the pf packetfilter engine. Whenever a state is closed or expired, pf exports the “life of the state” as a Netflow with IPFIX/NF5. No overheads, no interrim reporting, just exports when sessions expire.
IMPORTANT: The Greatest feature is you can enable it only the rules you wish to have flowstats for - no need to do it for everything.I installed pmacct on a linux, and used the builtin nfacctd tools (netflow collector) to collect the exported flows, and export them to a MariaDB/MySQL Database I designed with AI to minimize diskwriting. I’m using smart features to organize it in daily partitions for query effectiveness and quick “expiration” of old data (drop partition).
The database stores every flow exported for 30 days, so it can also be used for forensics. A DB procedure calculates various rollups stored in quickaccess tables at 5m, 1h, 1d intervals, plus creates roling snapshots of 7d and 30d usage stats.
Everything has been designed to be observed/graphed with Grafana, and I have designed some default dashboards for quickaccess like fx this dashboard: 24hour view of 1h - on the hour rollups:
I’m not sure how well it scales, but hundreds of clients and + 1mio flows a day is not a problem at all on a Pi5 with a SSD.
Right now it’s just flows and stats for what you define as local hosts, but I might do a round 2 to start enriching the data with DNS names - from KEA DHCP leases or worst case cached PTR lookups.
-
@keyser a docker of this that users could throw up quick would be great I think.
-
@johnpoz I’ll see about that. Right now i discovered a few issues I need to correct first, then I’ll have to learn how to create a container as I have never done docker before. Alternatively I might just supply the scripts and a highlevel guide.
I’ll be back in a few weeks
-
-
@luckman212 Yeah, I tried Akvorado and a few other open source collectors, but they are all VERY ressource intensive because they build on different large scale tech like Clickhouse, kafka, elasticsearch and so on.
While they do some enrichment (AS numbers fx), and have some interesting dashboards, they really do not offer anything interesting worth throwing so many ressources after it.The real gamechanger would be DNS enrichment based on correlation of DNS Query results from the client before it contacted the remote site. But this is a complicated setup and currently requires a full SIEM like fx. Greylog and a LOT of customization.
So I was thinking a small MariaDB database and Grafana would be more than fine, and like I said: It runs fine on a Raspberry Pi 4/5 with 4Gb RAM (so far). I only have tested up to a weeks worth of data so far, before scratching the setup to start over with a revised/better design/build schema to further decrease diskwriting.I’ll keep you posted, and if it gets to a resonable working state, I promise to share my setup in some form or other :-)
-
said in Made my own Netflow Collector…:
I’ll keep you posted, and if it gets to a resonable working state, I promise to share my setup in some form or other :-)
Okay, so after another week of optimizations and small design changes it seems to be running extremely well on my Pi4 with 8Gb RAM. It uses almost no ressources apart from memory and a little bit of disk I/O.
It can easily run on a 4Gb or likely even a 2Gb system as well, but right now my MariaDB settings file lets the SQL server use a full 4Gb Memory on its own, so that should be reduced for smaller memory systems.If anybody really would like to try my setup out, send me a PM, and i’ll put together a little quick guide (high level, basic Linux experience required). I’ll also include the configuration files, dashboards, and SQL scripts to create the setup.
EDIT: Notice, this collector is made VERY specifically for pflow(4) export which is what the pfSense+ Netflow exporter is using. Pflow(4) does not do interrim updates so data is ONLY reported once the firewall state is closed/timed out. The collector relies on this behavior, and a flow reported from a closing state is reported and attributed to the time the flow was exported/state closed. This save a BOATLOAD of SSD writes and data calculations compared to attributing it to the time the state was created/flow started.
Requirements:
- Linux system with 4Gb memory or more.
- 20+ GiB SSD diskspace depending on number of local hosts, rentention settings and flows generated every day. SDcard should be avoided because it will wear out to fast.
- MariaDB MySQL Server (opensource, available with most packaging systems)
- pmacct Netflow tools (opensource, available with most packaging systems)
- Grafana Server (opensource, available with most packaging systems)
NOTES: I don’t know how it scales in larger setups, but currently I’m building a lot of stats and host details in SQL and publish those in grafana designed rollup tables for very fast query, and a low diskwrite penalty to preserve SSD life.
Millions of flows every day is not a problem, but if you have hundreds of local devices, this might become the first obvious scaling problem. Diskspace usage and write intensity scales somewhat liniearly with the number of local clients in your network.
So until someone tests it on a larger scale consider this “home only with less than 100 active devices” to make sure it will perform well and not exaust a Pi4 ;-) -
I thought i’d just drop a slighty more understandable and descriptive explanation of what it is I have created:
Appliance Overview
The NetFlow/IPFIX Session Appliance is a lightweight network traffic analysis platform that collects IPFIX data from pfSense+ and other pflow(4) export capable devices. Rather than relying on external processing daemons or custom applications, the appliance performs all data processing inside MariaDB using native SQL objects.
Incoming flow records are written directly to the database by nfacctd. From that point onward, all processing—including flow correlation, session creation, statistical aggregation, retention management, and reporting—is handled entirely by SQL procedures, events, triggers, and views.
This architecture minimizes external dependencies, simplifies maintenance, and allows the complete data processing pipeline to reside within the database itself.
Architecture
The appliance consists of three primary components:
- nfacctd (pmacct) receives IPFIX exports from devices and inserts raw flow records into MariaDB.
- MariaDB performs all processing using native SQL. This includes:
- Sessionizing unidirectional NetFlow/IPFIX records into bidirectional conversations.
- Maintaining a session table optimized for reporting.
- Automatically generating 5-minute, hourly, and daily roll-up tables.
- Applying configurable retention policies to raw, session, and roll-up data.
- Grafana connects directly to MariaDB to display traffic statistics using the supplied dashboards.
The overall data flow is:
Flow Exporters → nfacctd → MariaDB (Sessionizer → Rollups → Retention) → Grafana
SQL-Based Processing
A key design goal of the appliance is that all data processing is implemented entirely in SQL.
After nfacctd stores raw IPFIX records, MariaDB automatically processes them through scheduled SQL events and stored procedures. The SQL-based sessionizer correlates matching unidirectional flow records into a single bidirectional session, producing a compact representation of each network conversation.
Additional SQL procedures continuously aggregate session data into 5-minute, hourly, and daily summary tables, providing efficient reporting over both recent and long-term historical data. Automatic SQL retention jobs remove expired data according to configurable retention periods while preserving aggregated historical statistics.
Because the complete processing pipeline is implemented inside the database, no external scripts, cron jobs, or custom processing applications are required beyond nfacctd for data collection and Grafana for visualization. This results in a simple, portable, and maintainable appliance where the database itself contains all processing logic.
-
@keyser Sounds really excellent - can't wait to give this a try!
-
@luckman212 said in Made my own Netflow Collector…:
@keyser Sounds really excellent - can't wait to give this a try!
I have a zip archive with the setup including a brief deployment guide ready, so PM me if you would like to give it spin ;-)
EDIT: It's not a Docker container. Haven't found the time to create and deploy those yet.FYI: All I expect back is a little feedback on issues/performance@scale, and that you do not share the setup with others :-)
Privacy Policy · Cookie Policy