Traffic Analysis & Protocol Breakdown — ClickHouse-Powered, sub-second on 1.6B rows

Traffic Analysis is the analytical backbone of RoutePulse — every flow record from every sFlow agent, every IPFIX template, every NetFlow v9 packet lands in a ClickHouse columnar store now running 44 tables (35 base + 9 materialized views) with 1.6 billion+ rows retained. The same engine that drives sub-second top-talker drilldowns also feeds the Conviction Engine’s 8-gate blackhole pipeline, the 18-model ML ensemble, and CAD’s per-(host, peer-class, hour) baselines. One ingest path, three consumers, zero ETL hops.

Multi-dimensional drilldowns span source/destination AS, IP, protocol, application, country and time window — every aggregation pre-computed in a materialized view so even 6-month spans return in <1 s. Deep Packet Inspection classifies 1,575 applications across 16 categories using 272 port-based rules at a 92% classification rate. A GeoIP traffic heatmap renders city-level MaxMind resolution on top of the GeoIP2 Commercial dataset (City + Country + ASN). Multi-protocol ingest covers sFlow v5 (sflow.org spec), IPFIX (RFC 7011/7012) and NetFlow v9 (RFC 3954) from 5 active flow sources, all enriched through a 3-tier ASN pipeline (LRU cache → direct RIB Patricia trie → WhoisService) hitting 98.8% IPv4 / 99.7% IPv6 attribution.

ClickHouse columnar store — 35 base tables + 9 materialized views

The raw flows table is the single source of truth; 9 materialized views (mv_flows_5m, mv_flows_1h, mv_flows_top_talkers, mv_flows_protocol, mv_flows_country, mv_flows_asn, mv_flows_dpi_app, mv_flows_router, mv_flows_geo_city) pre-aggregate at insert time so the UI never scans the raw table for analytic drilldowns. ZSTD compression at level 9 yields ~9.5× on real production data; 180-day retention is enforced per-partition by TTL. Tables are created programmatically by flow-collector-service.ts and ml-engine-service.ts so schema drift between code and production is impossible.

Multi-protocol ingest — sFlow v5 + IPFIX + NetFlow v9

The collector accepts sFlow v5 (UDP 6343), IPFIX templates per RFC 7011 with information elements from RFC 7012, and NetFlow v9 per RFC 3954 on a single ingest path. Template caching is per-(exporter, observation-domain). Flows are decoded, dual-LPM enriched against the live RIB and the Patricia trie ASN cache, then batch-inserted into ClickHouse at ~40 k flows/min sustained from 5 production exporters (FortiGate, Juniper MX, Huawei NE8000, MikroTik CCR2004 × 3 at SwissIX/MIX-IT/MINAP).

8 KPI cards · transit vs. peering · GeoIP heatmap

Eight KPI tiles surface total bytes/packets in/out, peak bandwidth, active protocols and top applications. The transit-vs-peering split distinguishes Colt / Cogent / Antenna / Lumen / RETN / UPC upstream from SwissIX / MIX-IT / MINAP IXP fabric. A GeoIP traffic heatmap layers MaxMind GeoIP2 Commercial city-level resolution on a Leaflet world map; the protocol-hierarchy view renders IPv4/IPv6 split, TCP/UDP/ICMP breakdowns and per-protocol byte/packet counts in a Wireshark-style tree.

Key Capabilities

• ClickHouse columnar engine — 44 tables (35 base + 9 materialized views), 1.6B+ flow rows, ~9.5× ZSTD compression, 180-day partitioned retention
• Multi-protocol ingest — sFlow v5 (sflow.org spec), IPFIX (RFC 7011 / RFC 7012), NetFlow v9 (RFC 3954) on a single UDP collector path
• Deep Packet Inspection — 1,575 application signatures across 16 categories, 272 port-based rules, 92% classification rate
• Pre-aggregated materialized views — mv_flows_5m, mv_flows_top_talkers, mv_flows_dpi_app, mv_flows_geo_city and 5 more keep top-N drilldowns <1 s on the full 1.6B-row dataset
• 3-tier ASN enrichment — 50K-entry LRU cache → direct RIB Patricia trie lookup (500 ms timeout) → WhoisService — 98.8% IPv4 / 99.7% IPv6 attribution
• 8 KPI tiles — total bytes/packets in/out, peak bandwidth, active protocols, top applications
• Transit-vs-peering analysis across Colt, Cogent, Antenna, Lumen, RETN, UPC and 3 IXPs (SwissIX, MIX-IT, MINAP)
• GeoIP traffic heatmap — MaxMind GeoIP2 Commercial (City + Country + ASN, 117 MB DB), city-level resolution, country breakdown tables
• Protocol hierarchy tree — IPv4/IPv6 split, TCP/UDP/ICMP byte/packet counts, per-port distribution analysis
• Direct feed to ConvictionEngine (8-gate blackhole pipeline) and CAD (per-host-class baselines) — one ingest path, three consumers
• Schema managed by flow-collector-service.ts + ml-engine-service.ts — no schema drift between code and production ClickHouse
• UTC-correct timestamps everywhere (now('UTC') guard documented in CLAUDE.md) — no CET timezone bugs on cross-shift queries

Engineered and operated by the GOLINE SOC & Network Engineering team.

Explore all RoutePulse features →