Building a NetFlow/IPFIX pipeline with Akvorado and ClickHouse for a Tier-2 ISP

Interface counters tell you that a 10G uplink is at 70 percent. They cannot tell you which destination AS, which customer prefix or which UDP port is responsible for it. Flow data answers exactly that question, and for a transit-buying ISP it is the difference between guessing at a peering decision and knowing.

Akvorado is a flow collector built for this shape of problem: it ingests NetFlow v9, IPFIX and sFlow, enriches every record with routing and interface context, and lands it in ClickHouse where you can query it in SQL. This is how the pieces fit together and where they break.

The pipeline, component by component#

Akvorado is three services plus two datastores:

• inlet — listens for flow packets on UDP, decodes the templates, applies enrichment, and produces records to Kafka. This is the only part that must keep up with line-rate bursts.
• Kafka — the buffer. It is what lets you restart ClickHouse, redeploy the schema or lose a consumer for ten minutes without losing flows.
• ClickHouse — the store. Raw flows plus aggregated rollups, each with its own retention.
• orchestrator — owns the ClickHouse schema and hands configuration to the other components. It creates the tables, the Kafka engine tables and the materialised views.
• console — the query API and the web UI on top.

The routers themselves are the exporters. Everything downstream is only as good as what they send.

Configuring the exporters#

The pattern is identical across vendors: define a sampler, define an exporter target, attach both to interfaces. Sample on ingress at the network edge, and be consistent about it — mixing ingress and egress sampling on the same path double-counts traffic.

Juniper (inline sampling, IPFIX)#

services {
    flow-monitoring {
        version-ipfix {
            template ipv4-tpl {
                flow-active-timeout 60;
                flow-inactive-timeout 15;
                template-refresh-rate seconds 30;
                option-refresh-rate seconds 30;
                ipv4-template;
            }
        }
    }
}
forwarding-options {
    sampling {
        instance flow-ins {
            input { rate 1000; }
            family inet {
                output {
                    flow-server 10.20.0.5 {
                        port 2055;
                        version-ipfix { template ipv4-tpl; }
                    }
                    inline-jflow { source-address 10.0.0.1; }
                }
            }
        }
    }
}
chassis { fpc 0 { sampling-instance flow-ins; } }

Cisco IOS XR#

flow exporter-map AKVORADO
 version v9
  options interface-table timeout 60
  options sampler-table timeout 60
  template data timeout 60
 !
 transport udp 2055
 source Loopback0
 destination 10.20.0.5
!
sampler-map SAMPLE-1K
 random 1 out-of 1000
!
flow monitor-map FMM-IPV4
 record ipv4
 exporter AKVORADO
 cache timeout active 60
!
interface HundredGigE0/0/0/0
 flow ipv4 monitor FMM-IPV4 sampler SAMPLE-1K ingress

MikroTik RouterOS#

/ip traffic-flow
set enabled=yes active-flow-timeout=1m inactive-flow-timeout=15s
/ip traffic-flow target
add dst-address=10.20.0.5 port=2055 version=ipfix v9-template-refresh=20

RouterOS does have a sampler, but it is off by default — packet-sampling, sampling-interval and sampling-space under /ip traffic-flow. With the configuration above, sampling disabled, it accounts for every packet: convenient for accuracy, expensive for CPU on a busy box, and it means this exporter must be registered in Akvorado with a sampling rate of 1, not the 1000 you configured on the routers next to it. If you do enable packet-sampling, register the rate it actually implies.

Sampling rate: the number that ruins everything#

Every byte count in the console is Bytes * SamplingRate. If a router samples 1:1000 and Akvorado believes it samples 1:1, every graph for that exporter is wrong by three orders of magnitude — and it will look plausible, because the shape of the curve is still correct.

Some platforms advertise their rate in IPFIX options templates and Akvorado picks it up automatically. Others do not, and you configure it per exporter subnet. Verify it once per exporter by comparing a flow-derived interface rate against the SNMP or gNMI counter for the same interface over the same window. If they agree within a few percent, the rate is right; if they differ by a factor that looks like your sampler configuration, it is not.

Sampling also has a statistical floor. The relative error on a flow scales roughly with the inverse square root of the number of sampled packets it contributed, so aggregate views over an hour are trustworthy while a single small flow in a one-minute bucket is not. Use flow data for proportions and rankings; use counters for absolute volume.

Enrichment: what turns records into answers#

A raw flow record contains addresses, ports, protocol, byte and packet counts and an ifIndex. On its own that is nearly useless. Akvorado attaches three kinds of context:

• Interface metadata by SNMP. The inlet polls each exporter to resolve ifIndex into ifName, ifDescr and speed, and you classify each interface as external (transit, peering, IX) or internal (customer, core) so the console can tell transit from on-net traffic.
• Routing data by BMP. Point the routers' BMP feed at Akvorado and every flow gains the real AS path, the communities and the next hop that the router actually used. This is far better than inferring origin AS from a GeoIP-style database, and it is what makes per-upstream analysis possible.
• Geo and ASN databases. Country and organisation names for addresses your routing table does not describe.

If the interface classification is wrong, every "transit versus peering" number in the console is wrong with it. Treat the boundary configuration as production config, in version control, reviewed like a router change.

ClickHouse: schema, rollups and retention#

The orchestrator creates a raw flows table plus a set of aggregated tables at coarser resolutions, each fed by a materialised view and each with its own TTL. The design intent is simple: keep full detail for a short window, keep aggregates for a long one.

Choose the resolutions from the questions you need to answer:

• Raw flows: incident forensics and abuse handling. Short retention — this is by far the largest table.
• One-minute aggregates: capacity and traffic engineering over days.
• One-hour aggregates: peering business cases and year-on-year comparisons. Cheap enough to keep for a long time.

Two practical points. Retention is a policy decision, not just a disk decision: flow records contain customer IP addresses, so the raw table's TTL is the answer to "how long do we keep data that identifies a subscriber". Write that down before someone asks. And keep Kafka retention longer than your worst realistic ClickHouse outage, because that retention window is your entire recovery capability.

Queries you will actually run#

The console covers the common views, but the interesting questions are SQL. Top destination networks over the last hour:

SELECT
    DstAS,
    sum(Bytes * SamplingRate) * 8 / 3600 AS bits_per_second
FROM flows
WHERE TimeReceived > now() - INTERVAL 1 HOUR
  AND OutIfBoundary = 'external'
GROUP BY DstAS
ORDER BY bits_per_second DESC
LIMIT 20

Which upstream is carrying a given destination AS, and how that splits:

SELECT
    ExporterName,
    OutIfName,
    sum(Bytes * SamplingRate) * 8 / 3600 AS bits_per_second
FROM flows
WHERE TimeReceived > now() - INTERVAL 1 HOUR
  AND DstAS = 15169
GROUP BY ExporterName, OutIfName
ORDER BY bits_per_second DESC

A sudden-onset check for a possible amplification attack — one destination, one amplifier service port, and a large fan-in of reflectors:

SELECT
    DstAddr,
    SrcPort,
    sum(Packets * SamplingRate) AS pps,
    uniq(SrcAddr) AS sources
FROM flows
WHERE TimeReceived > now() - INTERVAL 5 MINUTE
  AND Proto = 17
GROUP BY DstAddr, SrcPort
ORDER BY pps DESC
LIMIT 20

Column names follow the Akvorado schema; check them against your deployment's version before pasting these into a dashboard.

Failure modes#

• Sampling rate mismatch. Covered above, and by a distance the most common cause of a flow deployment nobody trusts.
• ifIndex drift. A line card reload renumbers indices; until the SNMP poller refreshes, traffic is attributed to the wrong interface or to none.
• Exporter source address. Akvorado keys exporters by the source IP of the flow packets. A router that sources from a different interface after a reboot appears as a brand new, unconfigured exporter with default settings.
• Asymmetric routing. Ingress-only sampling on an asymmetric path sees one direction. Interpret ratios accordingly.
• Tunnelled and MPLS traffic. Depending on platform and template, the exported keys may describe the outer header only. Know which of your paths this affects before you draw conclusions about them.
• UDP loss under load. Flow export is UDP. If the collector or the path to it is congested during exactly the incident you care about, records are dropped silently. Monitor the inlet's own drop counters as a first-class service metric.

Where this leads#

A flow pipeline is not the goal; it is the input. Once you can see per-upstream and per-destination-AS volumes with real AS paths attached, the next step is acting on them — shifting traffic between transits, justifying an IX port, or spotting a route leak by its traffic signature. That is covered in BGP traffic engineering with flow data.

For the counter side of the picture — the interface and queue metrics you correlate this against — see SNMP polling vs streaming telemetry.