This guide shows how to install and configure a complete NetFlow analyzer for MikroTik routers with pmacct, ClickHouse and Grafana on Ubuntu 24.04.
The router sends sampled NetFlow to a collector, the collector stores the traffic statistics in ClickHouse, and you get a command-line tool and Grafana dashboards that answer questions such as: who are the top talkers right now, which customer downloads the most, which ASNs the network exchanges traffic with and how much traffic flows between two autonomous systems. Every step below is a working configuration, so the guide can be followed from top to bottom.
Architecture
The platform uses three hosts. The IP addresses, AS number, customer network and passwords below are example values used throughout this guide (AS64500 and 198.51.100.0/24 are reserved for documentation). Replace them with your own before you start:
Host Role IP address
------------------ ------------------------------------- ------------
MikroTik router NetFlow v9 exporter (RouterOS 7) 10.10.10.1
flowcollector Collector: pmacct + ClickHouse 10.10.10.10
grafana Grafana 11 (existing server) 10.10.10.20
Local autonomous system: AS64500
Customer network behind the router: 198.51.100.0/24
Router uplink interfaces: sfp-sfpplus1, sfp-sfpplus2
ClickHouse user Password Used by
---------------- -------------- ------------------------------------
default ChAdmin2026 administration (clickhouse-client)
ingest NfIngest2026 ingest and ASN update scripts
grafana NfGrafana2026 Grafana and the nftop CLI
How the data flows through the platform:
MikroTik 10.10.10.1 (Traffic Flow, NetFlow v9, sampling 1:1000)
|
| UDP 2055
v
flowcollector 10.10.10.10
nfacctd (pmacct) restores the real volume (x1000) and aggregates
| the flows into 5-minute buckets
v
/var/spool/pmacct/flows-*.json one file per 5-minute bucket
|
v
pmacct-to-clickhouse.sh systemd timer, every minute
|
v
ClickHouse netflow.flows adds at insert time:
| as_src / as_dst origin ASN of the IPs
| direction in / out / internal / transit
| customer_ip local IP of the flow
| remote_as ASN on the external side
| keeps the data for 7 days
v
nftop (CLI on flowcollector) + Grafana 10.10.10.20 (port 8123)
The IP-to-ASN mapping is a static database from iptoasn.com, loaded into ClickHouse and refreshed weekly. No BGP session to the collector is needed. A collector VM with 2 vCPU and 16 GB RAM handles a router with up to 2 Gbps of traffic. Tested with pmacct 1.7.8, ClickHouse 26.9, Grafana 11.2 and RouterOS 7.22.
Prepare the Server
All steps from here until the Grafana section are executed on the collector (flowcollector, 10.10.10.10) as root. Set the timezone, install the base packages and create the working directories:
timedatectl set-timezone Etc/UTC
apt update
apt install -y pmacct curl gnupg ca-certificates python3-pip tcpdump
pip3 install --break-system-packages clickhouse-connect
mkdir -p /etc/pmacct /var/spool/pmacct /var/lib/netflow
The collector needs two inbound ports: UDP 2055 for NetFlow from the router and TCP 8123 for ClickHouse queries from Grafana. If UFW is enabled, allow them only from these two hosts:
ufw allow from 10.10.10.1 to any port 2055 proto udp
ufw allow from 10.10.10.20 to any port 8123 proto tcp
Install ClickHouse
ClickHouse is the column database that stores the flows. Add the official repository and install the server and client. When the installer asks for the password of the default user, enter ChAdmin2026:
curl -fsSL https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key | gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" > /etc/apt/sources.list.d/clickhouse.list
apt update
apt install -y clickhouse-server clickhouse-client
By default ClickHouse listens only on localhost. Allow connections from the network (needed for Grafana) and allow the default user to create the other users with SQL:
cat > /etc/clickhouse-server/config.d/listen.xml <<'EOF'
<clickhouse>
<listen_host>0.0.0.0</listen_host>
</clickhouse>
EOF
cat > /etc/clickhouse-server/users.d/access.xml <<'EOF'
<clickhouse><users><default><access_management>1</access_management></default></users></clickhouse>
EOF
systemctl enable --now clickhouse-server
Create the Database Schema
The schema creates the database, three users, the reference tables for ASNs and local networks, three in-memory dictionaries for fast lookups and the main flows table. Create the file:
mkdir -p /root/netflow && nano /root/netflow/schema.sql
Paste the schema. The passwords and the Grafana IP are the example values from the Architecture section:
CREATE DATABASE IF NOT EXISTS netflow;
-- users
CREATE USER IF NOT EXISTS dict_reader IDENTIFIED WITH no_password HOST LOCAL SETTINGS readonly = 2;
CREATE USER IF NOT EXISTS ingest IDENTIFIED WITH sha256_password BY 'NfIngest2026' HOST LOCAL;
CREATE USER IF NOT EXISTS grafana IDENTIFIED WITH sha256_password BY 'NfGrafana2026'
HOST IP '10.10.10.20', LOCAL SETTINGS readonly = 2;
-- IP-to-ASN reference tables
CREATE TABLE IF NOT EXISTS netflow.asn_prefixes
(
prefix String,
asn UInt32
)
ENGINE = MergeTree ORDER BY prefix;
CREATE TABLE IF NOT EXISTS netflow.asn_names
(
asn UInt32,
country LowCardinality(String),
name String,
updated DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(updated) ORDER BY asn;
-- local (customer) networks behind the router
CREATE TABLE IF NOT EXISTS netflow.local_prefixes
(
prefix String,
customer String DEFAULT ''
)
ENGINE = MergeTree ORDER BY prefix;
-- dictionaries
CREATE DICTIONARY IF NOT EXISTS netflow.asn_prefix
(
prefix String,
asn UInt32
)
PRIMARY KEY prefix
SOURCE(CLICKHOUSE(DB 'netflow' TABLE 'asn_prefixes' USER 'dict_reader'))
LAYOUT(IP_TRIE())
LIFETIME(MIN 3600 MAX 7200);
CREATE DICTIONARY IF NOT EXISTS netflow.asn_dict
(
asn UInt32,
name String,
country String
)
PRIMARY KEY asn
SOURCE(CLICKHOUSE(DB 'netflow' TABLE 'asn_names' USER 'dict_reader'))
LAYOUT(HASHED())
LIFETIME(MIN 3600 MAX 7200);
CREATE DICTIONARY IF NOT EXISTS netflow.local_net
(
prefix String,
customer String
)
PRIMARY KEY prefix
SOURCE(CLICKHOUSE(DB 'netflow' TABLE 'local_prefixes' USER 'dict_reader'))
LAYOUT(IP_TRIE())
LIFETIME(MIN 300 MAX 600);
-- flows: 5-minute buckets, enrichment at INSERT time, 7-day retention
CREATE TABLE IF NOT EXISTS netflow.flows
(
stamp_inserted DateTime,
stamp_updated DateTime DEFAULT now(),
peer_ip_src LowCardinality(String),
ip_src String,
ip_dst String,
port_src UInt16,
port_dst UInt16,
ip_proto UInt8,
as_src UInt32 MATERIALIZED dictGetOrDefault('netflow.asn_prefix', 'asn', tuple(toIPv6(ip_src)), 0),
as_dst UInt32 MATERIALIZED dictGetOrDefault('netflow.asn_prefix', 'asn', tuple(toIPv6(ip_dst)), 0),
src_local UInt8 MATERIALIZED dictHas('netflow.local_net', tuple(toIPv6(ip_src))),
dst_local UInt8 MATERIALIZED dictHas('netflow.local_net', tuple(toIPv6(ip_dst))),
direction LowCardinality(String) MATERIALIZED multiIf(src_local AND dst_local, 'internal', dst_local, 'in', src_local, 'out', 'transit'),
customer_ip String MATERIALIZED multiIf(direction = 'in', ip_dst, direction = 'out', ip_src, ''),
customer LowCardinality(String) MATERIALIZED multiIf(direction = 'in', dictGetOrDefault('netflow.local_net', 'customer', tuple(toIPv6(ip_dst)), ''), direction = 'out', dictGetOrDefault('netflow.local_net', 'customer', tuple(toIPv6(ip_src)), ''), ''),
remote_as UInt32 MATERIALIZED multiIf(direction = 'in', as_src, direction = 'out', as_dst, 0),
iface_in UInt32,
iface_out UInt32,
packets UInt64,
bytes UInt64,
flows UInt64
)
ENGINE = MergeTree
PARTITION BY toDate(stamp_inserted)
ORDER BY (stamp_inserted, as_src, as_dst, ip_src, ip_dst)
TTL stamp_inserted + INTERVAL 7 DAY DELETE
SETTINGS ttl_only_drop_parts = 1, index_granularity = 8192;
-- grants
GRANT SELECT ON netflow.asn_prefixes TO dict_reader;
GRANT SELECT ON netflow.asn_names TO dict_reader;
GRANT SELECT ON netflow.local_prefixes TO dict_reader;
GRANT INSERT, SELECT, OPTIMIZE, TRUNCATE, CREATE TABLE, DROP TABLE ON netflow.* TO ingest;
GRANT SYSTEM RELOAD DICTIONARY ON *.* TO ingest;
GRANT dictGet ON netflow.asn_prefix TO ingest, grafana;
GRANT dictGet ON netflow.asn_dict TO ingest, grafana;
GRANT dictGet ON netflow.local_net TO ingest, grafana;
GRANT SELECT ON netflow.* TO grafana;
Apply the schema with the default user (password ChAdmin2026) and check that all users and tables exist:
root@flowcollector:~# clickhouse-client --password --multiquery < /root/netflow/schema.sql
root@flowcollector:~# clickhouse-client --password -q "SHOW USERS; SHOW TABLES FROM netflow"
Password for user (default):
default
dict_reader
grafana
ingest
asn_dict
asn_names
asn_prefix
asn_prefixes
flows
local_net
local_prefixes
What the schema does:
dict_reader internal user without password (localhost only), used by the dictionaries
ingest writes flows and updates the ASN database (localhost only)
grafana read-only, allowed from Grafana 10.10.10.20 and from localhost (nftop)
asn_prefix IP prefix -> origin ASN, longest-prefix match (IP_TRIE)
asn_dict ASN -> name and country
local_net IP -> customer network (IP_TRIE), used for the traffic direction
flows one row per flow key and 5-minute bucket
as_src, as_dst, direction, customer_ip, customer and remote_as are MATERIALIZED columns: ClickHouse calculates them once when a row is inserted and stores them, so queries are fast and need no lookups. The table is partitioned by day, and TTL 7 DAY with ttl_only_drop_parts deletes each day automatically after 7 days.
Load the IP-to-ASN Database
The origin ASN of every IP comes from the free iptoasn.com dataset. It contains all announced IPv4 and IPv6 ranges with their origin ASN, country and AS name. The ClickHouse IP_TRIE dictionary needs CIDR prefixes instead of ranges, so a small Python script converts the file. Create the converter:
nano /usr/local/bin/build-networks-file.py
#!/usr/bin/env python3
# iptoasn.com ip2asn-combined.tsv.gz -> asn_prefixes.tsv (prefix, asn) + asn_names.tsv (asn, country, name)
import argparse, gzip, ipaddress, os, sys
ap = argparse.ArgumentParser()
ap.add_argument("--src", default="/var/lib/netflow/ip2asn-combined.tsv.gz")
ap.add_argument("--prefixes", default="/var/lib/netflow/asn_prefixes.tsv")
ap.add_argument("--asn-names", default="/var/lib/netflow/asn_names.tsv")
a = ap.parse_args()
names, n_pfx = {}, 0
with gzip.open(a.src, "rt", encoding="utf-8", errors="replace") as f, open(a.prefixes + ".tmp", "w") as out:
for line in f:
p = line.rstrip("\n").split("\t")
if len(p) < 5 or int(p[2]) == 0:
continue
asn = int(p[2])
names.setdefault(asn, (p[3], p[4].replace("\t", " ")))
try:
s, e = ipaddress.ip_address(p[0]), ipaddress.ip_address(p[1])
except ValueError:
continue
for net in ipaddress.summarize_address_range(s, e):
out.write(f"{net.with_prefixlen}\t{asn}\n")
n_pfx += 1
with open(a.asn_names + ".tmp", "w") as out:
for asn, (cc, desc) in sorted(names.items()):
out.write(f"{asn}\t{cc}\t{desc}\n")
os.replace(a.prefixes + ".tmp", a.prefixes)
os.replace(a.asn_names + ".tmp", a.asn_names)
print(f"prefixes: {n_pfx}, ASNs: {len(names)}", file=sys.stderr)
The update script downloads the dataset, converts it, loads the prefixes into a staging table, swaps it with the live table in one atomic step (EXCHANGE TABLES) and reloads the dictionaries. The collector keeps working during the update. Create the script:
nano /usr/local/bin/update-asn-db.sh
#!/bin/bash
set -euo pipefail
WORK=/var/lib/netflow
CH="clickhouse-client --host 127.0.0.1 --user ingest --password NfIngest2026"
mkdir -p "$WORK"
curl -fsSL -o "$WORK/ip2asn-combined.tsv.gz.tmp" https://iptoasn.com/data/ip2asn-combined.tsv.gz
mv "$WORK/ip2asn-combined.tsv.gz.tmp" "$WORK/ip2asn-combined.tsv.gz"
/usr/local/bin/build-networks-file.py \
--src "$WORK/ip2asn-combined.tsv.gz" \
--prefixes "$WORK/asn_prefixes.tsv" \
--asn-names "$WORK/asn_names.tsv"
$CH --query "CREATE TABLE IF NOT EXISTS netflow.asn_prefixes_new AS netflow.asn_prefixes"
$CH --query "TRUNCATE TABLE netflow.asn_prefixes_new"
$CH --query "INSERT INTO netflow.asn_prefixes_new (prefix, asn) FORMAT TSV" < "$WORK/asn_prefixes.tsv"
$CH --query "EXCHANGE TABLES netflow.asn_prefixes AND netflow.asn_prefixes_new"
$CH --query "SYSTEM RELOAD DICTIONARY netflow.asn_prefix"
$CH --query "INSERT INTO netflow.asn_names (asn, country, name) FORMAT TSV" < "$WORK/asn_names.tsv"
$CH --query "OPTIMIZE TABLE netflow.asn_names FINAL"
$CH --query "SYSTEM RELOAD DICTIONARY netflow.asn_dict"
echo "ASN db updated: $(date -Is) ($($CH --query 'SELECT count() FROM netflow.asn_prefixes') prefixes)"
Run the first import (about one minute) and test the lookup with Google DNS for IPv4 and a Google IPv6 address. Both must return AS15169:
root@flowcollector:~# chmod 755 /usr/local/bin/build-networks-file.py /usr/local/bin/update-asn-db.sh
root@flowcollector:~# /usr/local/bin/update-asn-db.sh
prefixes: 1022204, ASNs: 87182
ASN db updated: 2026-10-08T20:58:02+00:00 (1022204 prefixes)
root@flowcollector:~# clickhouse-client --password -q "SELECT dictGet('netflow.asn_prefix','asn',tuple(toIPv6('8.8.8.8'))), dictGet('netflow.asn_prefix','asn',tuple(toIPv6('2a00:1450:4001::1'))), dictGet('netflow.asn_dict','name',toUInt32(15169))"
Password for user (default):
15169 15169 GOOGLE
Define the Local Networks
ClickHouse must know which networks are behind the router to tell inbound from outbound traffic. Traffic to these networks is in (download of the customers), traffic from them is out (upload), and everything else is transit. Add the customer network:
root@flowcollector:~# clickhouse-client --password --multiquery <<'EOF'
INSERT INTO netflow.local_prefixes (prefix, customer) VALUES
('198.51.100.0/24', 'EXAMPLE-NET');
SYSTEM RELOAD DICTIONARY netflow.local_net;
SELECT * FROM netflow.local_prefixes;
EOF
198.51.100.0/24 EXAMPLE-NET
Optionally, add customer subnets with names. The more specific prefix wins, so the /24 stays the default for all other addresses, and the names appear in the customer reports:
INSERT INTO netflow.local_prefixes VALUES ('198.51.100.32/27', 'Customer-A'), ('198.51.100.64/26', 'Customer-B');
SYSTEM RELOAD DICTIONARY netflow.local_net;
Configure pmacct
nfacctd is the NetFlow daemon of pmacct. It receives the flows from the router, multiplies the sampled counters back to the real volume, groups the flows by IP addresses, ports, protocol and interfaces and writes one JSON file every 5 minutes. Create the configuration:
nano /etc/pmacct/nfacctd.conf
daemonize: false
pidfile: /run/nfacctd.pid
syslog: daemon
debug: false
nfacctd_port: 2055
nfacctd_renormalize: true
nfacctd_ext_sampling_rate: 1000
nfacctd_time_new: true
aggregate: peer_src_ip, src_host, dst_host, src_port, dst_port, proto, in_iface, out_iface, flows
plugins: print[ch]
print_output[ch]: json
print_num_protos[ch]: true
print_refresh_time[ch]: 300
print_history[ch]: 5m
print_history_roundoff[ch]: m
print_output_file[ch]: /var/spool/pmacct/flows-%Y%m%d-%H%M%S.json
print_output_file_append[ch]: false
print_cache_entries[ch]: 500000
plugin_pipe_size: 20480000
plugin_buffer_size: 20480
What the main settings do:
nfacctd_renormalize + ext_sampling_rate 1000 bytes and packets x1000 (MikroTik sampling 1/1000)
nfacctd_time_new: true bucket by arrival time on the collector
print_history 5m + refresh_time 300 5-minute buckets aligned to :00, :05, :10 ...
print_cache_entries 500000 ~2 GB RAM, unique keys per bucket
Create the systemd service for nfacctd:
nano /etc/systemd/system/nfacctd.service
[Unit]
Description=pmacct NetFlow collector (nfacctd)
After=network-online.target
Wants=network-online.target
[Service]
ExecStartPre=/bin/mkdir -p /var/spool/pmacct
ExecStart=/usr/sbin/nfacctd -f /etc/pmacct/nfacctd.conf
Restart=always
RestartSec=5
LimitNOFILE=65536
[Install]
WantedBy=multi-user.target
Configure the ClickHouse Ingest
A small script loads the JSON files from nfacctd into ClickHouse. It runs every minute, takes only finished files, inserts them into netflow.flows and deletes them. A file that cannot be inserted is renamed to .failed and stays in the spool directory. Create the script:
nano /usr/local/bin/pmacct-to-clickhouse.sh
#!/bin/bash
set -uo pipefail
SPOOL=/var/spool/pmacct
CH="clickhouse-client --host 127.0.0.1 --user ingest --password NfIngest2026"
exec 9>/run/lock/pmacct-ch.lock
flock -n 9 || exit 0
find "$SPOOL" -maxdepth 1 -type f -name 'flows-*.json' ! -newermt '-20 seconds' | sort | while read -r f; do
if [ ! -s "$f" ]; then rm -f "$f"; continue; fi
if $CH \
--input_format_skip_unknown_fields 1 \
--date_time_input_format best_effort \
--query "INSERT INTO netflow.flows FORMAT JSONEachRow" < "$f"; then
rm -f "$f"
else
echo "ingest failed: $f" >&2
mv "$f" "$f.failed"
fi
done
Run the script every minute with a systemd timer. Create /etc/systemd/system/pmacct-ch-ingest.service:
[Unit]
Description=Ingest pmacct JSON spool into ClickHouse
After=clickhouse-server.service
[Service]
Type=oneshot
ExecStart=/usr/local/bin/pmacct-to-clickhouse.sh
Create /etc/systemd/system/pmacct-ch-ingest.timer:
[Unit]
Description=Run pmacct -> ClickHouse ingest every minute
[Timer]
OnCalendar=*:*:30
AccuracySec=5s
Persistent=true
[Install]
WantedBy=timers.target
The ASN database is refreshed every Sunday at 04:00 with a second timer. Create /etc/systemd/system/netflow-asn-update.service:
[Unit]
Description=Refresh IP->ASN database in ClickHouse
After=network-online.target clickhouse-server.service
Wants=network-online.target
[Service]
Type=oneshot
ExecStart=/usr/local/bin/update-asn-db.sh
Create /etc/systemd/system/netflow-asn-update.timer:
[Unit]
Description=Weekly ASN database refresh
[Timer]
OnCalendar=Sun 04:00
RandomizedDelaySec=1h
Persistent=true
[Install]
WantedBy=timers.target
Start the Services
Make the scripts executable and start nfacctd and both timers. nfacctd must report waiting for NetFlow/IPFIX data on :::2055 within a few seconds:
root@flowcollector:~# chmod 755 /usr/local/bin/pmacct-to-clickhouse.sh
root@flowcollector:~# systemctl daemon-reload
root@flowcollector:~# systemctl enable --now nfacctd pmacct-ch-ingest.timer netflow-asn-update.timer
root@flowcollector:~# journalctl -u nfacctd -n 8 --no-pager | grep -vE 'build='
Oct 09 00:01:12 flowcollector nfacctd[6125]: INFO ( default/core ): NetFlow Accounting Daemon, nfacctd 1.7.8-git (RELEASE)
Oct 09 00:01:12 flowcollector nfacctd[6125]: INFO ( default/core ): Reading configuration file '/etc/pmacct/nfacctd.conf'.
Oct 09 00:01:12 flowcollector nfacctd[6125]: INFO ( ch/print ): plugin_pipe_size=20480000 bytes plugin_buffer_size=20480 bytes
Oct 09 00:01:12 flowcollector nfacctd[6125]: INFO ( default/core ): waiting for NetFlow/IPFIX data on :::2055
Oct 09 00:01:12 flowcollector nfacctd[6132]: INFO ( ch/print ): cache entries=500000 base cache memory=2068000000 bytes
Oct 09 00:01:13 flowcollector nfacctd[6132]: INFO ( ch/print ): JSON: setting object handlers.
root@flowcollector:~# systemctl list-timers --no-pager | grep -E 'pmacct|netflow'
Thu 2026-10-09 00:02:30 UTC 14s - - pmacct-ch-ingest.timer pmacct-ch-ingest.service
Sun 2026-10-11 04:06:02 UTC 2 days - - netflow-asn-update.timer netflow-asn-update.service
Configure MikroTik Traffic Flow
On the MikroTik router, enable Traffic Flow on the uplink interfaces with 1:1000 packet sampling (one packet out of every 1000 is exported, which keeps the router CPU load low) and add the collector as a NetFlow v9 target. NTP keeps the router clock correct:
/ip traffic-flow
set enabled=yes interfaces=sfp-sfpplus1,sfp-sfpplus2 cache-entries=4M \
active-flow-timeout=30s inactive-flow-timeout=15s \
packet-sampling=yes packet-sampling-interval=1 packet-sampling-space=1000
/ip traffic-flow target
add src-address=10.10.10.1 dst-address=10.10.10.10 port=2055 version=9 \
v9-template-refresh=20 v9-template-timeout=60
/system ntp client set enabled=yes servers=pool.ntp.org,time.cloudflare.com
NetFlow v9 records are described by templates. v9-template-refresh=20 and v9-template-timeout=60 make the router resend them every 20 packets or 60 seconds, so the collector always knows the record format.
On the collector, check that the NetFlow packets arrive:
root@flowcollector:~# tcpdump -ni any udp port 2055 -c 3
listening on any, link-type LINUX_SLL2 (Linux cooked v2), snapshot length 262144 bytes
00:02:44.043790 enp1s0 In IP 10.10.10.1.2055 > 10.10.10.10.2055: UDP, length 236
00:02:45.073801 enp1s0 In IP 10.10.10.1.2055 > 10.10.10.10.2055: UDP, length 684
00:02:47.153817 enp1s0 In IP 10.10.10.1.2055 > 10.10.10.10.2055: UDP, length 872
Verify the Pipeline
The data appears with a delay of one bucket: the bucket 10:05-10:10 is written by nfacctd at 10:10:00 and is in ClickHouse at about 10:10:30. Wait until the next 5-minute boundary plus 30 seconds. The following status script shows every stage of the pipeline on one screen:
nano /usr/local/bin/nfstatus
#!/bin/bash
CH="clickhouse-client --user ingest --password NfIngest2026"
echo "== now: $(date '+%H:%M:%S') nfacctd: $(systemctl is-active nfacctd) since $(systemctl show nfacctd -p ActiveEnterTimestamp --value | cut -d' ' -f3)"
echo "== spool:"; ls -la /var/spool/pmacct/ | grep flows || echo " (empty)"
echo "== last purges (QN = written records):"
journalctl -u nfacctd --since "-30 min" --no-pager | grep -i "purging cache - END" | tail -3 | sed 's/.*INFO/ INFO/'
echo "== ingest errors:"; journalctl -u pmacct-ch-ingest --since "-30 min" --no-pager | grep -iE 'failed|error' | tail -3
echo "== buckets in ClickHouse:"
$CH -q "SELECT stamp_inserted, count() rows, round(sum(bytes)*8/300/1e6,1) mbps, uniq(as_src) src_asns FROM netflow.flows GROUP BY stamp_inserted ORDER BY stamp_inserted DESC LIMIT 6 FORMAT PrettyCompactNoEscapes"
root@flowcollector:~# chmod 755 /usr/local/bin/nfstatus
root@flowcollector:~# nfstatus
== now: 00:15:56 nfacctd: active since 00:06:56
== spool:
(empty)
== last purges (QN = written records):
INFO ( ch/print ): *** Purging cache - END (PID: 6335, QN: 205/205, ET: 0) ***
INFO ( ch/print ): *** Purging cache - END (PID: 6428, QN: 386/386, ET: 0) ***
== ingest errors:
== buckets in ClickHouse:
┌──────stamp_inserted─┬─rows─┬─mbps─┬─src_asns─┐
1. │ 2026-10-09 00:10:00 │ 386 │ 23.5 │ 70 │
└─────────────────────┴──────┴──────┴──────────┘
The number of rows in ClickHouse must match QN (written records) of the last nfacctd purge, and src_asns must be greater than 0, which confirms that the ASN lookup works. Check the traffic direction. in and out must carry the traffic, and transit should be close to 0:
root@flowcollector:~# clickhouse-client --password -q "SELECT direction, count() rows, round(sum(bytes)*8/900/1e6,1) mbps FROM netflow.flows WHERE stamp_inserted > now()-INTERVAL 15 MINUTE GROUP BY direction FORMAT PrettyCompact"
┌─direction─┬─rows─┬─mbps─┐
1. │ transit │ 3 │ 0 │
2. │ out │ 274 │ 1.9 │
3. │ in │ 373 │ 12.5 │
└───────────┴──────┴──────┘
Compare the Mbps with the router counters at the same moment. The values must be of the same order:
/interface monitor-traffic sfp-sfpplus1,sfp-sfpplus2 once
Install the nftop CLI
nftop is a small command-line tool for quick checks directly on the collector, for example the top talkers of the last 10 minutes. Bandwidth is always the average for the selected period: sum(bytes) * 8 / seconds. The periods are aligned to the 5-minute buckets. Create the script:
nano /usr/local/bin/nftop
#!/usr/bin/env python3
"""nftop - top talkers from ClickHouse (pmacct 5-minute buckets).
nftop --by dst_ip --last 5m
nftop --by l4 --last 10m --top 10
nftop --by src_asn --last 1h
nftop --by remote_asn --last 1h
nftop --dir in --by customer_ip
nftop --by cust_asn --dir in
nftop --remote-asn 15169 --by customer_ip
nftop --from "2026-10-09 10:00" --to "2026-10-09 12:00" --by dst_asn
"""
import argparse, datetime as dt, os, re, sys
import clickhouse_connect
CH_HOST = os.environ.get("CH_HOST", "127.0.0.1")
CH_USER = os.environ.get("CH_USER", "grafana")
CH_PASS = os.environ.get("CH_PASS", "NfGrafana2026")
BUCKET = 300
PROTO = {1: "icmp", 6: "tcp", 17: "udp", 47: "gre", 50: "esp", 58: "icmp6", 132: "sctp"}
ASN_COLS = ("as_src", "as_dst", "remote_as")
DIMS = {
"src_ip": (["ip_src"], ["src_ip"]),
"dst_ip": (["ip_dst"], ["dst_ip"]),
"pair": (["ip_src", "ip_dst"], ["src_ip", "dst_ip"]),
"l4": (["ip_src", "port_src", "ip_dst", "port_dst", "ip_proto"], ["src_ip", "sport", "dst_ip", "dport", "proto"]),
"src_asn": (["as_src"], ["src_asn"]),
"dst_asn": (["as_dst"], ["dst_asn"]),
"asn_pair": (["as_src", "as_dst"], ["src_asn", "dst_asn"]),
"port": (["port_dst", "ip_proto"], ["dport", "proto"]),
"proto": (["ip_proto"], ["proto"]),
"exporter": (["peer_ip_src"], ["exporter"]),
"customer_ip": (["customer_ip"], ["customer_ip"]),
"customer": (["customer"], ["customer"]),
"direction": (["direction"], ["direction"]),
"remote_asn": (["remote_as"], ["remote_asn"]),
"cust_asn": (["customer_ip", "remote_as"], ["customer_ip", "remote_asn"]),
}
def parse_last(s):
m = re.fullmatch(r"(\d+)([smhd])", s)
if not m:
raise argparse.ArgumentTypeError("format: 5m, 10m, 1h, 1d")
return dt.timedelta(**{{"s": "seconds", "m": "minutes", "h": "hours", "d": "days"}[m.group(2)]: int(m.group(1))})
def fmt(v, units, base):
for u in units:
if v < base:
return f"{v:7.2f} {u}"
v /= base
return f"{v:7.2f} {units[-1]}"
def floor5(t):
return t.replace(second=0, microsecond=0, minute=t.minute - t.minute % 5)
def main():
ap = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
ap.add_argument("--by", choices=DIMS.keys(), default="src_ip")
ap.add_argument("--last", type=parse_last, default=parse_last("10m"))
ap.add_argument("--from", dest="t_from")
ap.add_argument("--to", dest="t_to")
ap.add_argument("--top", type=int, default=20)
ap.add_argument("--asn", type=int, help="src or dst ASN")
ap.add_argument("--remote-asn", type=int)
ap.add_argument("--ip", help="src or dst IP")
ap.add_argument("--proto", type=int)
ap.add_argument("--exporter")
ap.add_argument("--dir", choices=["in", "out", "internal", "transit"])
ap.add_argument("--customer")
ap.add_argument("--sort", choices=["bytes", "packets", "flows"], default="bytes")
a = ap.parse_args()
now = dt.datetime.now()
if a.t_from:
t_from = floor5(dt.datetime.fromisoformat(a.t_from))
t_to = floor5(dt.datetime.fromisoformat(a.t_to)) if a.t_to else floor5(now)
else:
t_to = floor5(now)
t_from = t_to - dt.timedelta(seconds=max(1, int(a.last.total_seconds() // BUCKET)) * BUCKET)
period = (t_to - t_from).total_seconds()
if period <= 0:
sys.exit("empty period (minimum 5 min)")
cols, heads = DIMS[a.by]
where = ["stamp_inserted >= %(f)s", "stamp_inserted < %(t)s"]
params = {"f": t_from, "t": t_to, "lim": a.top}
filters = [
(a.asn, "(as_src = %(asn)s OR as_dst = %(asn)s)", "asn"),
(a.remote_asn, "remote_as = %(ras)s", "ras"),
(a.ip, "(ip_src = %(ip)s OR ip_dst = %(ip)s)", "ip"),
(a.proto, "ip_proto = %(p)s", "p"),
(a.exporter, "peer_ip_src = %(ex)s", "ex"),
(a.dir, "direction = %(dir)s", "dir"),
(a.customer, "customer = %(cu)s", "cu"),
]
for val, cond, key in filters:
if val is not None:
where.append(cond)
params[key] = val
order = {"bytes": "b", "packets": "p", "flows": "fl"}[a.sort]
w = " AND ".join(where)
c = ", ".join(cols)
client = clickhouse_connect.get_client(host=CH_HOST, username=CH_USER, password=CH_PASS)
rows = client.query(f"SELECT {c}, sum(bytes) b, sum(packets) p, sum(flows) fl FROM netflow.flows "
f"WHERE {w} GROUP BY {c} ORDER BY {order} DESC LIMIT %(lim)s", parameters=params).result_rows
total = client.query(f"SELECT sum(bytes), sum(packets), sum(flows) FROM netflow.flows WHERE {w}",
parameters=params).result_rows[0]
names = {}
asns = {r[i] for r in rows for i, col in enumerate(cols) if col in ASN_COLS}
if asns:
names = dict(client.query("SELECT asn, name FROM netflow.asn_names FINAL WHERE asn IN %(l)s",
parameters={"l": list(asns)}).result_rows)
print(f"Period: {t_from} -> {t_to} ({period/60:.0f} min) top {a.top} by {a.by}, sorted by {a.sort}")
print(f"Total: {fmt(total[0]*8/period, ['bps','Kbps','Mbps','Gbps','Tbps'], 1000)} avg | "
f"{fmt(total[0], ['B','KB','MB','GB','TB'], 1024)} | {total[1]:,} pkts | {total[2]:,} flows\n")
hdr = heads + ["avg_bps", "bytes", "packets", "flows", "%"]
print(" ".join(f"{h:<22}" if h.endswith("asn") else f"{h:<16}" for h in hdr))
for r in rows:
out = []
for i, col in enumerate(cols):
v = r[i]
if col == "ip_proto":
v = PROTO.get(v, str(v))
if col in ASN_COLS:
out.append(f"{('AS' + str(v) + ' ' + names.get(v, ''))[:22]:<22}")
else:
out.append(f"{str(v):<16}")
b, pk, fl = r[len(cols):]
pct = 100.0 * b / total[0] if total[0] else 0
print(" ".join(out) + f" {fmt(b*8/period, ['bps','Kbps','Mbps','Gbps','Tbps'], 1000):<16} "
f"{fmt(b, ['B','KB','MB','GB','TB'], 1024):<16} {pk:<16,} {fl:<16,} {pct:5.1f}")
if __name__ == "__main__":
main()
Make it executable and store the password of the grafana user in the environment:
root@flowcollector:~# chmod 755 /usr/local/bin/nftop
root@flowcollector:~# echo 'export CH_PASS=NfGrafana2026' >> /root/.bashrc && source /root/.bashrc
Top source ASNs for the last 5-minute bucket:
root@flowcollector:~# nftop --by src_asn --last 5m
Period: 2026-10-09 00:10:00 -> 2026-10-09 00:15:00 (5 min) top 20 by src_asn, sorted by bytes
Total: 23.45 Mbps avg | 838.74 MB | 1,005,000 pkts | 583 flows
src_asn avg_bps bytes packets flows %
AS15169 GOOGLE 8.91 Mbps 318.70 MB 267,000 87 38.0
AS13335 CLOUDFLARENET 3.75 Mbps 133.95 MB 34,000 12 16.0
AS60068 CDN77 _ 2.59 Mbps 92.65 MB 21,000 1 11.0
AS54113 FASTLY 734.93 Kbps 26.28 MB 23,000 7 3.1
AS16509 AMAZON-02 704.88 Kbps 25.21 MB 50,000 35 3.0
AS32590 VALVE-CORPORAT 572.69 Kbps 20.48 MB 26,000 9 2.4
AS24940 HETZNER-AS 528.85 Kbps 18.91 MB 16,000 9 2.3
Top customer IPs by download:
root@flowcollector:~# nftop --dir in --by customer_ip --last 5m --top 5
Period: 2026-10-09 00:40:00 -> 2026-10-09 00:45:00 (5 min) top 5 by customer_ip, sorted by bytes
Total: 15.71 Mbps avg | 562.00 MB | 557,000 pkts | 264 flows
customer_ip avg_bps bytes packets flows %
198.51.100.92 4.90 Mbps 175.34 MB 131,000 30 31.2
198.51.100.36 3.80 Mbps 135.94 MB 110,000 15 24.2
198.51.100.49 2.98 Mbps 106.55 MB 55,000 30 19.0
198.51.100.85 901.65 Kbps 32.25 MB 10,000 9 5.7
198.51.100.51 727.15 Kbps 26.00 MB 29,000 8 4.6
Remote ASNs the customers exchange traffic with, in both directions:
root@flowcollector:~# nftop --by remote_asn --last 10m --top 5
Period: 2026-10-09 00:45:00 -> 2026-10-09 00:55:00 (10 min) top 5 by remote_asn, sorted by bytes
Total: 25.74 Mbps avg | 1.80 GB | 2,122,000 pkts | 1,007 flows
remote_asn avg_bps bytes packets flows %
AS15169 GOOGLE 15.42 Mbps 1.08 GB 1,002,000 235 59.9
AS3320 DTAG Internet s 1.99 Mbps 142.20 MB 326,000 176 7.7
AS54113 FASTLY 1.96 Mbps 140.40 MB 42,000 10 7.6
AS13335 CLOUDFLARENET 1.95 Mbps 139.76 MB 70,000 42 7.6
AS2906 AS-SSI 1.41 Mbps 100.65 MB 24,000 17 5.5
Available reports: src_ip, dst_ip, pair, l4, src_asn, dst_asn, asn_pair, port, proto, exporter, customer_ip, customer, direction, remote_asn, cust_asn. Filters: --ip, --asn, --remote-asn, --proto, --dir, --customer, --exporter, --from/--to, --sort.
Connect Grafana
The following commands run on the Grafana server (10.10.10.20). Install the official ClickHouse data source plugin from Grafana Labs and give the plugin directory to the grafana user, otherwise Grafana cannot load it:
root@grafana:~# grafana-cli plugins install grafana-clickhouse-datasource
root@grafana:~# chown -R grafana:grafana /var/lib/grafana/plugins
root@grafana:~# systemctl restart grafana-server
root@grafana:~# grep 'Plugin registered.*clickhouse' /var/log/grafana/grafana.log | tail -1
logger=plugins.registration level=info msg="Plugin registered" pluginId=grafana-clickhouse-datasource
Test that the Grafana server can query ClickHouse on the collector. The result is the number of rows in the flows table:
root@grafana:~# curl -s "http://10.10.10.10:8123/?user=grafana&password=NfGrafana2026" --data "SELECT count() FROM netflow.flows"
1132
In the Grafana web interface, open Connections -> Data sources -> Add data source and select ClickHouse (the plugin by Grafana Labs). Set the following values and leave everything else at the defaults:
Server address 10.10.10.10
Server port 8123
Protocol HTTP
Secure connection off
Username grafana
Password NfGrafana2026
Default database netflow
Click Save & test. The expected result is Data source is working.
Import the NetFlow Dashboard
The first dashboard gives an overview of the traffic, top talkers, customers and ASN statistics. Import it in Grafana: Dashboards -> New -> Import, paste the JSON below into Import via dashboard JSON model, click Load, select the ClickHouse data source and click Import.
{
"__inputs": [{"name": "DS_CLICKHOUSE", "label": "ClickHouse", "type": "datasource", "pluginId": "grafana-clickhouse-datasource", "pluginName": "ClickHouse"}],
"__requires": [{"type": "datasource", "id": "grafana-clickhouse-datasource", "name": "ClickHouse", "version": "4.0.0"}],
"title": "NetFlow — Top Talkers & ASN",
"description": "Data are 5-minute aggregates from pmacct. The minimum panel interval is 5m.",
"uid": "netflow-pmacct-ch",
"tags": ["netflow", "pmacct", "clickhouse"],
"timezone": "browser",
"editable": true,
"graphTooltip": 1,
"schemaVersion": 39,
"version": 1,
"refresh": "5m",
"time": {"from": "now-15m", "to": "now"},
"timepicker": {"refresh_intervals": ["1m", "5m", "15m"], "quick_ranges": [{"display": "Last 5 min (1 bucket)", "from": "now-5m", "to": "now"}, {"display": "Last 10 min", "from": "now-10m", "to": "now"}, {"display": "Last 15 min", "from": "now-15m", "to": "now"}, {"display": "Last 30 min", "from": "now-30m", "to": "now"}, {"display": "Last 1 hour", "from": "now-1h", "to": "now"}, {"display": "Last 24 hours", "from": "now-24h", "to": "now"}, {"display": "Last 7 days", "from": "now-7d", "to": "now"}]},
"templating": {"list": [
{"name": "exporter", "label": "Exporter", "type": "query", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "includeAll": true, "multi": true, "allValue": "", "current": {"text": "All", "value": "$__all"}, "refresh": 2, "query": "SELECT DISTINCT peer_ip_src FROM netflow.flows WHERE stamp_inserted > now() - INTERVAL 1 DAY", "definition": "distinct exporters (24h)", "sort": 1},
{"name": "dim", "label": "Top talkers by", "type": "custom", "query": "Top SRC IP : ip_src, Top DST IP : ip_dst, SRC IP → DST IP : ip_src || ' → ' || ip_dst, L4: SRC IP:port → DST IP:port/proto : ip_src || ':' || toString(port_src) || ' → ' || ip_dst || ':' || toString(port_dst) || ' ' || multiIf(ip_proto=6\\,'tcp'\\,ip_proto=17\\,'udp'\\,toString(ip_proto)), Top SRC ASN : 'AS' || toString(as_src) || ' ' || dictGetOrDefault('netflow.asn_dict'\\, 'name'\\, as_src\\, ''), Top DST ASN : 'AS' || toString(as_dst) || ' ' || dictGetOrDefault('netflow.asn_dict'\\, 'name'\\, as_dst\\, ''), SRC ASN → DST ASN : 'AS' || toString(as_src) || ' → AS' || toString(as_dst), Top DST port : multiIf(ip_proto=6\\,'tcp/'\\,ip_proto=17\\,'udp/'\\,toString(ip_proto)||'/') || toString(port_dst), Top exporter : peer_ip_src, Top customer IP : customer_ip, Top customer (name) : customer, Direction : direction, Top remote ASN : 'AS' || toString(remote_as) || ' ' || dictGetOrDefault('netflow.asn_dict'\\, 'name'\\, remote_as\\, '')", "options": [{"text": "Top SRC IP", "value": "ip_src", "selected": true}, {"text": "Top DST IP", "value": "ip_dst", "selected": false}, {"text": "SRC IP → DST IP", "value": "ip_src || ' → ' || ip_dst", "selected": false}, {"text": "L4: SRC IP:port → DST IP:port/proto", "value": "ip_src || ':' || toString(port_src) || ' → ' || ip_dst || ':' || toString(port_dst) || ' ' || multiIf(ip_proto=6,'tcp',ip_proto=17,'udp',toString(ip_proto))", "selected": false}, {"text": "Top SRC ASN", "value": "'AS' || toString(as_src) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', as_src, '')", "selected": false}, {"text": "Top DST ASN", "value": "'AS' || toString(as_dst) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', as_dst, '')", "selected": false}, {"text": "SRC ASN → DST ASN", "value": "'AS' || toString(as_src) || ' → AS' || toString(as_dst)", "selected": false}, {"text": "Top DST port", "value": "multiIf(ip_proto=6,'tcp/',ip_proto=17,'udp/',toString(ip_proto)||'/') || toString(port_dst)", "selected": false}, {"text": "Top exporter", "value": "peer_ip_src", "selected": false}, {"text": "Top customer IP", "value": "customer_ip", "selected": false}, {"text": "Top customer (name)", "value": "customer", "selected": false}, {"text": "Direction", "value": "direction", "selected": false}, {"text": "Top remote ASN", "value": "'AS' || toString(remote_as) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', remote_as, '')", "selected": false}], "current": {"text": "Top SRC IP", "value": "ip_src"}, "multi": false, "includeAll": false},
{"name": "topn", "label": "Top N", "type": "custom", "query": "10,20,50,100,200", "options": [{"text": "10", "value": "10", "selected": false}, {"text": "20", "value": "20", "selected": true}, {"text": "50", "value": "50", "selected": false}, {"text": "100", "value": "100", "selected": false}, {"text": "200", "value": "200", "selected": false}], "current": {"text": "20", "value": "20"}},
{"name": "proto", "label": "Protocol", "type": "custom", "query": "All : 0, TCP : 6, UDP : 17, ICMP : 1, GRE : 47, ESP : 50", "options": [{"text": "All", "value": "0", "selected": true}, {"text": "TCP", "value": "6", "selected": false}, {"text": "UDP", "value": "17", "selected": false}, {"text": "ICMP", "value": "1", "selected": false}, {"text": "GRE", "value": "47", "selected": false}, {"text": "ESP", "value": "50", "selected": false}], "current": {"text": "All", "value": "0"}},
{"name": "dir", "label": "Direction", "type": "custom", "query": "All : all, Inbound (to customers) : in, Outbound (from customers) : out, Internal : internal, Transit : transit", "options": [{"text": "All", "value": "all", "selected": true}, {"text": "Inbound (to customers)", "value": "in", "selected": false}, {"text": "Outbound (from customers)", "value": "out", "selected": false}, {"text": "Internal", "value": "internal", "selected": false}, {"text": "Transit", "value": "transit", "selected": false}], "current": {"text": "All", "value": "all"}},
{"name": "ip", "label": "IP filter (src or dst)", "type": "textbox", "query": "", "current": {"text": "", "value": ""}, "options": []},
{"name": "asn_filter", "label": "ASN filter (src or dst)", "type": "textbox", "query": "0", "current": {"text": "0", "value": "0"}, "options": []},
{"name": "asn_a", "label": "ASN A (local AS first)", "type": "query", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "query": "SELECT asn\nFROM (SELECT as_src AS asn, sum(bytes) b FROM netflow.flows WHERE stamp_inserted > now() - INTERVAL 1 DAY GROUP BY asn\n UNION ALL SELECT as_dst, sum(bytes) FROM netflow.flows WHERE stamp_inserted > now() - INTERVAL 1 DAY GROUP BY as_dst)\nGROUP BY asn HAVING asn > 0\nORDER BY (asn IN (SELECT DISTINCT as_dst FROM netflow.flows WHERE stamp_inserted > now() - INTERVAL 1 DAY AND direction = 'in')) DESC, sum(b) DESC\nLIMIT 2000", "refresh": 1, "definition": "local AS first, then by traffic 24h", "current": {"text": "", "value": ""}, "sort": 0},
{"name": "asn_b", "label": "ASN B (remote, by traffic)", "type": "query", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "query": "SELECT remote_as AS asn FROM netflow.flows\nWHERE stamp_inserted > now() - INTERVAL 1 DAY AND remote_as > 0\nGROUP BY asn ORDER BY sum(bytes) DESC LIMIT 2000", "refresh": 1, "definition": "remote ASNs by traffic 24h", "current": {"text": "", "value": ""}, "sort": 0}
]},
"annotations": {"list": []},
"links": [],
"panels": [
{"id": 1, "type": "row", "title": "Overview", "collapsed": false, "gridPos": {"x": 0, "y": 0, "w": 24, "h": 1}, "panels": []},
{"id": 2, "title": "Avg bandwidth (period)", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 1, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "fixed", "fixedColor": "blue"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 3, "title": "Total bytes", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 4, "y": 1, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bytes", "color": {"mode": "fixed", "fixedColor": "purple"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 4, "title": "Avg pps", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 8, "y": 1, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(packets)/dateDiff('second', $__fromTime, $__toTime) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "pps", "color": {"mode": "fixed", "fixedColor": "green"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 5, "title": "Flows / s", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 12, "y": 1, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(flows)/dateDiff('second', $__fromTime, $__toTime) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "short", "color": {"mode": "fixed", "fixedColor": "orange"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 6, "title": "Unique src IPs", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 16, "y": 1, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT uniq(ip_src) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "short", "color": {"mode": "fixed", "fixedColor": "yellow"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 7, "title": "Unique dst IPs", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 20, "y": 1, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT uniq(ip_dst) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "short", "color": {"mode": "fixed", "fixedColor": "red"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 8, "title": "Inbound avg (to customers)", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 5, "w": 6, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='in'", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "fixed", "fixedColor": "green"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 9, "title": "Outbound avg (from customers)", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 6, "y": 5, "w": 6, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='out'", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "fixed", "fixedColor": "orange"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 10, "title": "Internal avg", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 12, "y": 5, "w": 6, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='internal'", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "fixed", "fixedColor": "blue"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 11, "title": "Transit avg (not in local_prefixes)", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 18, "y": 5, "w": 6, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='transit'", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "fixed", "fixedColor": "red"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 12, "title": "Total traffic (bps) by exporter", "type": "timeseries", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 9, "w": 16, "h": 9}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT $__timeInterval(stamp_inserted) AS t, peer_ip_src AS exporter, sum(bytes)*8/$__interval_s AS bps FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') GROUP BY t, exporter ORDER BY t", "format": 0, "queryType": "timeseries", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "palette-classic"}, "custom": {"drawStyle": "line", "lineWidth": 1, "fillOpacity": 25, "gradientMode": "opacity", "lineInterpolation": "smooth", "stacking": {"mode": "normal"}, "showPoints": "never"}}, "overrides": []}, "options": {"legend": {"displayMode": "table", "placement": "bottom", "showLegend": true, "calcs": ["mean", "max", "lastNotNull"]}, "tooltip": {"mode": "multi", "sort": "desc"}}},
{"id": 13, "title": "Protocols", "type": "piechart", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 16, "y": 9, "w": 8, "h": 9}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT multiIf(ip_proto=6,'TCP',ip_proto=17,'UDP',ip_proto=1,'ICMP',ip_proto=47,'GRE',ip_proto=50,'ESP',toString(ip_proto)) AS k, sum(bytes) AS bytes\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') GROUP BY k ORDER BY bytes DESC LIMIT 8", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bytes", "color": {"mode": "palette-classic"}}, "overrides": []}, "options": {"pieType": "donut", "displayLabels": ["percent"], "legend": {"displayMode": "list", "placement": "right", "showLegend": true, "values": ["percent"]}, "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^bytes$/", "values": true}, "tooltip": {"mode": "single"}}},
{"id": 14, "title": "Inbound vs Outbound (bps)", "type": "timeseries", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 18, "w": 16, "h": 8}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT $__timeInterval(stamp_inserted) AS t,\n sum(if(direction='in', bytes, 0))*8/$__interval_s AS \"Inbound to customers\",\n sum(if(direction='out', bytes, 0))*8/$__interval_s AS \"Outbound from customers\",\n sum(if(direction='transit', bytes, 0))*8/$__interval_s AS \"Transit\"\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') GROUP BY t ORDER BY t", "format": 0, "queryType": "timeseries", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "palette-classic"}, "custom": {"drawStyle": "line", "lineWidth": 1, "fillOpacity": 25, "gradientMode": "opacity", "lineInterpolation": "smooth", "stacking": {"mode": "none"}, "showPoints": "never"}}, "overrides": []}, "options": {"legend": {"displayMode": "table", "placement": "bottom", "showLegend": true, "calcs": ["mean", "max", "lastNotNull"]}, "tooltip": {"mode": "multi", "sort": "desc"}}},
{"id": 15, "title": "Direction", "type": "piechart", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 16, "y": 18, "w": 8, "h": 8}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT direction AS k, sum(bytes) AS bytes\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') GROUP BY k ORDER BY bytes DESC LIMIT 8", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bytes", "color": {"mode": "palette-classic"}}, "overrides": []}, "options": {"pieType": "donut", "displayLabels": ["percent"], "legend": {"displayMode": "list", "placement": "right", "showLegend": true, "values": ["percent"]}, "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^bytes$/", "values": true}, "tooltip": {"mode": "single"}}},
{"id": 16, "type": "row", "title": "Top talkers — dimension selected by ${dim} variable", "collapsed": false, "gridPos": {"x": 0, "y": 26, "w": 24, "h": 1}, "panels": []},
{"id": 17, "title": "Top ${topn} — ${dim:text}", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 27, "w": 14, "h": 12}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT ${dim} AS \"key\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')\nGROUP BY \"key\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 18, "title": "Top 10 — ${dim:text} (avg bps)", "type": "bargauge", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 14, "y": 27, "w": 10, "h": 12}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT ${dim} AS k, sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') GROUP BY k ORDER BY avg_bps DESC LIMIT 10", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "decimals": 1, "color": {"mode": "continuous-BlPu"}}, "overrides": []}, "options": {"displayMode": "gradient", "orientation": "horizontal", "namePlacement": "left", "showUnfilled": true, "valueMode": "color", "sizing": "auto", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^avg_bps$/", "values": true}}},
{"id": 19, "title": "Top 10 ${dim:text} over time (bps)", "type": "timeseries", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 39, "w": 24, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT $__timeInterval(stamp_inserted) AS t, ${dim} AS k, sum(bytes)*8/$__interval_s AS bps\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')\n AND ${dim} IN (SELECT ${dim} FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') GROUP BY ${dim} ORDER BY sum(bytes) DESC LIMIT 10)\nGROUP BY t, k ORDER BY t", "format": 0, "queryType": "timeseries", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "palette-classic"}, "custom": {"drawStyle": "line", "lineWidth": 1, "fillOpacity": 25, "gradientMode": "opacity", "lineInterpolation": "smooth", "stacking": {"mode": "none"}, "showPoints": "never"}}, "overrides": []}, "options": {"legend": {"displayMode": "table", "placement": "bottom", "showLegend": true, "calcs": ["mean", "max", "lastNotNull"]}, "tooltip": {"mode": "multi", "sort": "desc"}}},
{"id": 20, "type": "row", "title": "Customers (local_prefixes)", "collapsed": false, "gridPos": {"x": 0, "y": 49, "w": 24, "h": 1}, "panels": []},
{"id": 21, "title": "Top ${topn} customer IPs — download (in)", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 50, "w": 12, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT customer_ip AS \"customer_ip\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='in'),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='in'\nGROUP BY \"customer_ip\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 22, "title": "Top ${topn} customer IPs — upload (out)", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 12, "y": 50, "w": 12, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT customer_ip AS \"customer_ip\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='out'),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='out'\nGROUP BY \"customer_ip\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 23, "title": "Top customers by name — download", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 60, "w": 12, "h": 8}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT customer AS \"customer\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='in' AND customer != ''),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='in' AND customer != ''\nGROUP BY \"customer\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 24, "title": "Top customers by name — upload", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 12, "y": 60, "w": 12, "h": 8}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT customer AS \"customer\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='out' AND customer != ''),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='out' AND customer != ''\nGROUP BY \"customer\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 25, "title": "Top 10 customer IPs over time — download (bps)", "type": "timeseries", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 68, "w": 24, "h": 9}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT $__timeInterval(stamp_inserted) AS t, customer_ip AS k, sum(bytes)*8/$__interval_s AS bps\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='in'\n AND customer_ip IN (SELECT customer_ip FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND direction='in' GROUP BY customer_ip ORDER BY sum(bytes) DESC LIMIT 10)\nGROUP BY t, k ORDER BY t", "format": 0, "queryType": "timeseries", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "palette-classic"}, "custom": {"drawStyle": "line", "lineWidth": 1, "fillOpacity": 25, "gradientMode": "opacity", "lineInterpolation": "smooth", "stacking": {"mode": "none"}, "showPoints": "never"}}, "overrides": []}, "options": {"legend": {"displayMode": "table", "placement": "bottom", "showLegend": true, "calcs": ["mean", "max", "lastNotNull"]}, "tooltip": {"mode": "multi", "sort": "desc"}}},
{"id": 26, "type": "row", "title": "ASN statistics", "collapsed": false, "gridPos": {"x": 0, "y": 77, "w": 24, "h": 1}, "panels": []},
{"id": 27, "title": "Top ${topn} remote ASN (external side of customer traffic)", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 78, "w": 12, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT 'AS' || toString(remote_as) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', remote_as, '') AS \"remote_asn\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND remote_as != 0),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND remote_as != 0\nGROUP BY \"remote_asn\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 28, "title": "Top ${topn} customer IP → remote ASN", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 12, "y": 78, "w": 12, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT customer_ip || ' → ' || 'AS' || toString(remote_as) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', remote_as, '') AS \"cust_asn\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND remote_as != 0),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND remote_as != 0\nGROUP BY \"cust_asn\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 29, "title": "Top 10 remote ASN over time (bps)", "type": "timeseries", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 88, "w": 24, "h": 9}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT $__timeInterval(stamp_inserted) AS t, 'AS' || toString(remote_as) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', remote_as, '') AS k, sum(bytes)*8/$__interval_s AS bps\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND remote_as != 0\n AND remote_as IN (SELECT remote_as FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND remote_as != 0 GROUP BY remote_as ORDER BY sum(bytes) DESC LIMIT 10)\nGROUP BY t, k ORDER BY t", "format": 0, "queryType": "timeseries", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "palette-classic"}, "custom": {"drawStyle": "line", "lineWidth": 1, "fillOpacity": 25, "gradientMode": "opacity", "lineInterpolation": "smooth", "stacking": {"mode": "normal"}, "showPoints": "never"}}, "overrides": []}, "options": {"legend": {"displayMode": "table", "placement": "bottom", "showLegend": true, "calcs": ["mean", "max", "lastNotNull"]}, "tooltip": {"mode": "multi", "sort": "desc"}}},
{"id": 30, "title": "Top ${topn} source ASN", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 97, "w": 12, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT 'AS' || toString(as_src) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', as_src, '') AS \"src_asn\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')\nGROUP BY \"src_asn\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 31, "title": "Top ${topn} destination ASN", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 12, "y": 97, "w": 12, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT 'AS' || toString(as_dst) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', as_dst, '') AS \"dst_asn\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')\nGROUP BY \"dst_asn\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 32, "title": "Top ${topn} ASN pairs (src → dst)", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 107, "w": 24, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT 'AS' || toString(as_src) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', as_src, '') || ' → ' || 'AS' || toString(as_dst) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', as_dst, '') AS \"asn_pair\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}')\nGROUP BY \"asn_pair\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 33, "type": "row", "title": "Traffic between ASN A (${asn_a}) and ASN B (${asn_b})", "collapsed": false, "gridPos": {"x": 0, "y": 117, "w": 24, "h": 1}, "panels": []},
{"id": 34, "title": "ASN A ↔ ASN B", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 118, "w": 24, "h": 3}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT 'AS' || toString(toUInt32(${asn_a})) || ' ' || dictGetOrDefault('netflow.asn_dict','name',toUInt32(${asn_a}),'?') || ' ↔ AS' || toString(toUInt32(${asn_b})) || ' ' || dictGetOrDefault('netflow.asn_dict','name',toUInt32(${asn_b}),'?') AS names", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"color": {"mode": "fixed", "fixedColor": "text"}}, "overrides": []}, "options": {"colorMode": "none", "graphMode": "none", "textMode": "value", "justifyMode": "center", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^names$/", "values": false}}},
{"id": 35, "title": "ASN A → B / B → A (bps)", "type": "timeseries", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 121, "w": 16, "h": 9}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT $__timeInterval(stamp_inserted) AS t,\n sum(if(as_src = ${asn_a} AND as_dst = ${asn_b}, bytes, 0))*8/$__interval_s AS \"A → B\",\n sum(if(as_src = ${asn_b} AND as_dst = ${asn_a}, bytes, 0))*8/$__interval_s AS \"B → A\"\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND ((as_src = ${asn_a} AND as_dst = ${asn_b}) OR (as_src = ${asn_b} AND as_dst = ${asn_a})) GROUP BY t ORDER BY t", "format": 0, "queryType": "timeseries", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "palette-classic"}, "custom": {"drawStyle": "line", "lineWidth": 1, "fillOpacity": 25, "gradientMode": "opacity", "lineInterpolation": "smooth", "stacking": {"mode": "none"}, "showPoints": "never"}}, "overrides": []}, "options": {"legend": {"displayMode": "table", "placement": "bottom", "showLegend": true, "calcs": ["mean", "max", "lastNotNull"]}, "tooltip": {"mode": "multi", "sort": "desc"}}},
{"id": 36, "title": "Avg A → B", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 16, "y": 121, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND as_src = ${asn_a} AND as_dst = ${asn_b}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "fixed", "fixedColor": "blue"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 37, "title": "Avg B → A", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 20, "y": 121, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND as_src = ${asn_b} AND as_dst = ${asn_a}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "fixed", "fixedColor": "purple"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 38, "title": "Bytes A → B", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 16, "y": 125, "w": 4, "h": 5}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND as_src = ${asn_a} AND as_dst = ${asn_b}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bytes", "color": {"mode": "fixed", "fixedColor": "blue"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 39, "title": "Bytes B → A", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 20, "y": 125, "w": 4, "h": 5}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND as_src = ${asn_b} AND as_dst = ${asn_a}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bytes", "color": {"mode": "fixed", "fixedColor": "purple"}, "decimals": 2}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "area", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "", "values": false}}},
{"id": 40, "title": "Top IP pairs A → B", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 130, "w": 12, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT ip_src || ':' || toString(port_src) || ' → ' || ip_dst || ':' || toString(port_dst) || ' / ' || toString(ip_proto) AS \"flow\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND as_src = ${asn_a} AND as_dst = ${asn_b}),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND as_src = ${asn_a} AND as_dst = ${asn_b}\nGROUP BY \"flow\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 41, "title": "Top IP pairs B → A", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 12, "y": 130, "w": 12, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT ip_src || ':' || toString(port_src) || ' → ' || ip_dst || ':' || toString(port_dst) || ' / ' || toString(ip_proto) AS \"flow\",\n sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps,\n sum(bytes) AS total_bytes, sum(packets) AS total_packets, sum(flows) AS total_flows,\n round(100*sum(bytes)/(SELECT sum(bytes) FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND as_src = ${asn_b} AND as_dst = ${asn_a}),2) AS pct\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') AND as_src = ${asn_b} AND as_dst = ${asn_a}\nGROUP BY \"flow\" ORDER BY total_bytes DESC LIMIT ${topn}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true}}, "overrides": [{"matcher": {"id": "byName", "options": "avg_bps"}, "properties": [{"id": "unit", "value": "bps"}, {"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}]}, {"matcher": {"id": "byName", "options": "total_packets"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "packets"}]}, {"matcher": {"id": "byName", "options": "total_flows"}, "properties": [{"id": "unit", "value": "short"}, {"id": "displayName", "value": "flows"}]}, {"matcher": {"id": "byName", "options": "pct"}, "properties": [{"id": "unit", "value": "percent"}, {"id": "custom.cellOptions", "value": {"type": "color-background", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-GrYlRd"}}]}]}, "options": {"showHeader": true, "sortBy": [{"displayName": "bytes", "desc": true}], "footer": {"show": false}}},
{"id": 42, "type": "row", "title": "Layer 4", "collapsed": false, "gridPos": {"x": 0, "y": 140, "w": 24, "h": 1}, "panels": []},
{"id": 43, "title": "Top dst ports (avg bps)", "type": "bargauge", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 141, "w": 8, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT multiIf(ip_proto=6,'tcp/',ip_proto=17,'udp/',toString(ip_proto)||'/') || toString(port_dst) AS k, sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') GROUP BY k ORDER BY avg_bps DESC LIMIT 15", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "decimals": 1, "color": {"mode": "continuous-BlPu"}}, "overrides": []}, "options": {"displayMode": "gradient", "orientation": "horizontal", "namePlacement": "left", "showUnfilled": true, "valueMode": "color", "sizing": "auto", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^avg_bps$/", "values": true}}},
{"id": 44, "title": "Top src ports (avg bps)", "type": "bargauge", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 8, "y": 141, "w": 8, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT multiIf(ip_proto=6,'tcp/',ip_proto=17,'udp/',toString(ip_proto)||'/') || toString(port_src) AS k, sum(bytes)*8/dateDiff('second', $__fromTime, $__toTime) AS avg_bps\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') GROUP BY k ORDER BY avg_bps DESC LIMIT 15", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "decimals": 1, "color": {"mode": "continuous-BlPu"}}, "overrides": []}, "options": {"displayMode": "gradient", "orientation": "horizontal", "namePlacement": "left", "showUnfilled": true, "valueMode": "color", "sizing": "auto", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^avg_bps$/", "values": true}}},
{"id": 45, "title": "Traffic by protocol (bps)", "type": "timeseries", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 16, "y": 141, "w": 8, "h": 10}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT $__timeInterval(stamp_inserted) AS t, multiIf(ip_proto=6,'TCP',ip_proto=17,'UDP',ip_proto=1,'ICMP',toString(ip_proto)) AS proto, sum(bytes)*8/$__interval_s AS bps FROM netflow.flows WHERE $__timeFilter(stamp_inserted) AND peer_ip_src IN (${exporter:singlequote}) AND (${proto} = 0 OR ip_proto = ${proto}) AND ('${ip}' = '' OR ip_src = '${ip}' OR ip_dst = '${ip}') AND (${asn_filter} = 0 OR as_src = ${asn_filter} OR as_dst = ${asn_filter}) AND ('${dir}' = 'all' OR direction = '${dir}') GROUP BY t, proto ORDER BY t", "format": 0, "queryType": "timeseries", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "palette-classic"}, "custom": {"drawStyle": "line", "lineWidth": 1, "fillOpacity": 25, "gradientMode": "opacity", "lineInterpolation": "smooth", "stacking": {"mode": "normal"}, "showPoints": "never"}}, "overrides": []}, "options": {"legend": {"displayMode": "table", "placement": "bottom", "showLegend": true, "calcs": ["mean", "max", "lastNotNull"]}, "tooltip": {"mode": "multi", "sort": "desc"}}}
]
}
Dashboard sections:
Overview avg bandwidth, pps, flows/s, unique IPs, inbound / outbound / internal / transit,
traffic per exporter, inbound vs outbound, protocols, direction
Top talkers table, bar gauge and time series for the dimension selected in "Top talkers by"
Customers top customer IPs by download and upload, customers by name
ASN statistics top remote ASN, customer IP -> remote ASN, top src / dst ASN, ASN pairs
ASN A <-> ASN B traffic between two ASNs in both directions, top IP pairs
Layer 4 top source and destination ports, traffic per protocol
For the ASN A <-> ASN B section, select your own AS as ASN A (AS64500 in this example) and a remote network as ASN B, for example AS15169, to see the traffic between them in both directions. Use the variables at the top of the dashboard to change the view: Top talkers by (source IP, destination IP, IP pair, L4, source ASN, destination ASN, ASN pair, port, customer IP, remote ASN), Top N, Exporter, Protocol, Direction, IP filter, ASN filter, ASN A and ASN B. The time picker selects the period, and all bandwidth values are averages for it.
Import the Custom Flows Dashboard
The second dashboard is an ad-hoc report builder: choose the columns, add WHERE filters, set the sort order and limit, and get a table with a TOTAL row. Import it the same way:
{
"__inputs": [{"name": "DS_CLICKHOUSE", "label": "ClickHouse", "type": "datasource", "pluginId": "grafana-clickhouse-datasource", "pluginName": "ClickHouse"}],
"__requires": [{"type": "datasource", "id": "grafana-clickhouse-datasource", "name": "ClickHouse", "version": "4.0.0"}],
"title": "Netflow - Custom Flows",
"uid": "netflow-custom-flows",
"tags": ["netflow", "clickhouse", "custom"],
"description": "Ad-hoc flow reports: selectable columns (GROUP BY), WHERE filters, ORDER BY, LIMIT and a TOTAL row. Mode Current (last 5-minute bucket) or Average (time range).",
"timezone": "browser",
"editable": true,
"graphTooltip": 1,
"schemaVersion": 39,
"version": 1,
"refresh": "",
"time": {"from": "now-1h", "to": "now"},
"timepicker": {"quick_ranges": [{"display": "Last 5 min", "from": "now-5m", "to": "now"}, {"display": "Last 15 min", "from": "now-15m", "to": "now"}, {"display": "Last 1 hour", "from": "now-1h", "to": "now"}, {"display": "Last 24 hours", "from": "now-24h", "to": "now"}, {"display": "Last 7 days", "from": "now-7d", "to": "now"}]},
"templating": {"list": [
{"name": "period", "label": "Mode", "type": "custom", "multi": false, "includeAll": false, "description": "Current = last complete 5-minute bucket, independent of the time picker. Average = average for the selected time range.", "query": "Average (time range) : range, Current (last 5-minute bucket) : last5", "options": [{"text": "Average (time range)", "value": "range", "selected": false}, {"text": "Current (last 5-minute bucket)", "value": "last5", "selected": true}], "current": {"text": "Current (last 5-minute bucket)", "value": "last5"}},
{"name": "cols", "label": "TABLE COLUMNS", "type": "custom", "multi": true, "includeAll": false, "description": "Columns to display and group by", "query": "IP_SRC : ip_src AS IP_SRC, IP_DST : ip_dst AS IP_DST, AS_SRC : 'AS' || toString(as_src) || ' ' || dictGetOrDefault('netflow.asn_dict'\\, 'name'\\, as_src\\, '') AS AS_SRC, AS_DST : 'AS' || toString(as_dst) || ' ' || dictGetOrDefault('netflow.asn_dict'\\, 'name'\\, as_dst\\, '') AS AS_DST, PORT_SRC : port_src AS PORT_SRC, PORT_DST : port_dst AS PORT_DST, PROTO : multiIf(ip_proto=6\\,'tcp'\\,ip_proto=17\\,'udp'\\,ip_proto=1\\,'icmp'\\,ip_proto=47\\,'gre'\\,toString(ip_proto)) AS PROTO, DIRECTION : direction AS DIRECTION, CUSTOMER_IP : customer_ip AS CUSTOMER_IP, IFACE_IN : iface_in AS IFACE_IN, IFACE_OUT : iface_out AS IFACE_OUT, EXPORTER : peer_ip_src AS EXPORTER", "options": [{"text": "IP_SRC", "value": "ip_src AS IP_SRC", "selected": true}, {"text": "IP_DST", "value": "ip_dst AS IP_DST", "selected": true}, {"text": "AS_SRC", "value": "'AS' || toString(as_src) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', as_src, '') AS AS_SRC", "selected": false}, {"text": "AS_DST", "value": "'AS' || toString(as_dst) || ' ' || dictGetOrDefault('netflow.asn_dict', 'name', as_dst, '') AS AS_DST", "selected": false}, {"text": "PORT_SRC", "value": "port_src AS PORT_SRC", "selected": false}, {"text": "PORT_DST", "value": "port_dst AS PORT_DST", "selected": true}, {"text": "PROTO", "value": "multiIf(ip_proto=6,'tcp',ip_proto=17,'udp',ip_proto=1,'icmp',ip_proto=47,'gre',toString(ip_proto)) AS PROTO", "selected": true}, {"text": "DIRECTION", "value": "direction AS DIRECTION", "selected": false}, {"text": "CUSTOMER_IP", "value": "customer_ip AS CUSTOMER_IP", "selected": false}, {"text": "IFACE_IN", "value": "iface_in AS IFACE_IN", "selected": false}, {"text": "IFACE_OUT", "value": "iface_out AS IFACE_OUT", "selected": false}, {"text": "EXPORTER", "value": "peer_ip_src AS EXPORTER", "selected": false}], "current": {"text": ["IP_SRC", "IP_DST", "PORT_DST", "PROTO"], "value": ["ip_src AS IP_SRC", "ip_dst AS IP_DST", "port_dst AS PORT_DST", "multiIf(ip_proto=6,'tcp',ip_proto=17,'udp',ip_proto=1,'icmp',ip_proto=47,'gre',toString(ip_proto)) AS PROTO"]}},
{"name": "order", "label": "ORDER BY", "type": "custom", "multi": false, "includeAll": false, "description": "", "query": "Mbps : Mbps, kpps : kpps, bytes : total_bytes, flows : flows_cnt", "options": [{"text": "Mbps", "value": "Mbps", "selected": true}, {"text": "kpps", "value": "kpps", "selected": false}, {"text": "bytes", "value": "total_bytes", "selected": false}, {"text": "flows", "value": "flows_cnt", "selected": false}], "current": {"text": "Mbps", "value": "Mbps"}},
{"name": "limit", "label": "LIMIT", "type": "custom", "multi": false, "includeAll": false, "description": "", "query": "10 : 10, 20 : 20, 50 : 50, 100 : 100, 200 : 200, 500 : 500", "options": [{"text": "10", "value": "10", "selected": false}, {"text": "20", "value": "20", "selected": true}, {"text": "50", "value": "50", "selected": false}, {"text": "100", "value": "100", "selected": false}, {"text": "200", "value": "200", "selected": false}, {"text": "500", "value": "500", "selected": false}], "current": {"text": "20", "value": "20"}},
{"name": "w_ip_src", "label": "WHERE IP_SRC", "type": "textbox", "query": "", "description": "exact IP; empty = no filter", "current": {"text": "", "value": ""}, "options": [{"text": "", "value": "", "selected": true}]},
{"name": "w_ip_dst", "label": "WHERE IP_DST", "type": "textbox", "query": "", "description": "exact IP; empty = no filter", "current": {"text": "", "value": ""}, "options": [{"text": "", "value": "", "selected": true}]},
{"name": "w_as_src", "label": "WHERE AS_SRC", "type": "textbox", "query": "0", "description": "ASN number; 0 = no filter", "current": {"text": "0", "value": "0"}, "options": [{"text": "0", "value": "0", "selected": true}]},
{"name": "w_as_dst", "label": "WHERE AS_DST", "type": "textbox", "query": "0", "description": "ASN number; 0 = no filter", "current": {"text": "0", "value": "0"}, "options": [{"text": "0", "value": "0", "selected": true}]},
{"name": "w_dir", "label": "WHERE DIRECTION", "type": "custom", "multi": false, "includeAll": false, "description": "", "query": "all : all, in (to customers) : in, out (from customers) : out, internal : internal, transit : transit", "options": [{"text": "all", "value": "all", "selected": true}, {"text": "in (to customers)", "value": "in", "selected": false}, {"text": "out (from customers)", "value": "out", "selected": false}, {"text": "internal", "value": "internal", "selected": false}, {"text": "transit", "value": "transit", "selected": false}], "current": {"text": "all", "value": "all"}},
{"name": "w_port_src", "label": "WHERE PORT_SRC", "type": "textbox", "query": "0", "description": "L4 port; 0 = no filter", "current": {"text": "0", "value": "0"}, "options": [{"text": "0", "value": "0", "selected": true}]},
{"name": "w_port_dst", "label": "WHERE PORT_DST", "type": "textbox", "query": "0", "description": "L4 port; 0 = no filter", "current": {"text": "0", "value": "0"}, "options": [{"text": "0", "value": "0", "selected": true}]},
{"name": "w_proto", "label": "WHERE PROTO", "type": "custom", "multi": false, "includeAll": false, "description": "", "query": "all : 0, tcp : 6, udp : 17, icmp : 1, gre : 47", "options": [{"text": "all", "value": "0", "selected": true}, {"text": "tcp", "value": "6", "selected": false}, {"text": "udp", "value": "17", "selected": false}, {"text": "icmp", "value": "1", "selected": false}, {"text": "gre", "value": "47", "selected": false}], "current": {"text": "all", "value": "0"}},
{"name": "w_extra", "label": "WHERE extra (SQL)", "type": "textbox", "query": "1", "description": "free ClickHouse SQL expression, for example: port_dst = 443 AND ip_dst LIKE '198.51.100.%' | customer_ip = '198.51.100.50' | as_src IN (15169, 13335)", "current": {"text": "1", "value": "1"}, "options": [{"text": "1", "value": "1", "selected": true}]}
]},
"annotations": {"list": []},
"links": [{"title": "NetFlow — Top Talkers & ASN", "type": "link", "url": "/d/netflow-pmacct-ch", "icon": "dashboard"}],
"panels": [
{"id": 1, "title": "TOTAL Mbps (all matched rows)", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 0, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT round(sum(bytes)*8/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e6, 2) AS Mbps, round(sum(packets)/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e3, 2) AS kpps,\n sum(bytes) AS total_bytes, uniq(ip_src) src_ips, uniq(ip_dst) dst_ips, count() AS rows_matched,\n if('${period}'='range', concat(toString($__fromTime), ' → ', toString($__toTime)),\n concat('bucket ', toString((SELECT max(stamp_inserted) FROM netflow.flows)), ' (+5 min)')) AS period_label\nFROM netflow.flows WHERE (('${period}' = 'range' AND $__timeFilter(stamp_inserted)) OR ('${period}' = 'last5' AND stamp_inserted = (SELECT max(stamp_inserted) FROM netflow.flows))) AND ('${w_ip_src}' = '' OR ip_src = '${w_ip_src}') AND ('${w_ip_dst}' = '' OR ip_dst = '${w_ip_dst}') AND (${w_as_src} = 0 OR as_src = ${w_as_src}) AND (${w_as_dst} = 0 OR as_dst = ${w_as_dst}) AND ('${w_dir}' = 'all' OR direction = '${w_dir}') AND (${w_proto} = 0 OR ip_proto = ${w_proto}) AND (${w_port_src} = 0 OR port_src = ${w_port_src}) AND (${w_port_dst} = 0 OR port_dst = ${w_port_dst}) AND (${w_extra})", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "none", "decimals": 2, "color": {"mode": "fixed", "fixedColor": "blue"}}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "none", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^Mbps$/", "values": false}}},
{"id": 2, "title": "TOTAL kpps", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 4, "y": 0, "w": 4, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT round(sum(bytes)*8/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e6, 2) AS Mbps, round(sum(packets)/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e3, 2) AS kpps,\n sum(bytes) AS total_bytes, uniq(ip_src) src_ips, uniq(ip_dst) dst_ips, count() AS rows_matched,\n if('${period}'='range', concat(toString($__fromTime), ' → ', toString($__toTime)),\n concat('bucket ', toString((SELECT max(stamp_inserted) FROM netflow.flows)), ' (+5 min)')) AS period_label\nFROM netflow.flows WHERE (('${period}' = 'range' AND $__timeFilter(stamp_inserted)) OR ('${period}' = 'last5' AND stamp_inserted = (SELECT max(stamp_inserted) FROM netflow.flows))) AND ('${w_ip_src}' = '' OR ip_src = '${w_ip_src}') AND ('${w_ip_dst}' = '' OR ip_dst = '${w_ip_dst}') AND (${w_as_src} = 0 OR as_src = ${w_as_src}) AND (${w_as_dst} = 0 OR as_dst = ${w_as_dst}) AND ('${w_dir}' = 'all' OR direction = '${w_dir}') AND (${w_proto} = 0 OR ip_proto = ${w_proto}) AND (${w_port_src} = 0 OR port_src = ${w_port_src}) AND (${w_port_dst} = 0 OR port_dst = ${w_port_dst}) AND (${w_extra})", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "none", "decimals": 2, "color": {"mode": "fixed", "fixedColor": "green"}}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "none", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^kpps$/", "values": false}}},
{"id": 3, "title": "Bytes", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 8, "y": 0, "w": 3, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT round(sum(bytes)*8/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e6, 2) AS Mbps, round(sum(packets)/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e3, 2) AS kpps,\n sum(bytes) AS total_bytes, uniq(ip_src) src_ips, uniq(ip_dst) dst_ips, count() AS rows_matched,\n if('${period}'='range', concat(toString($__fromTime), ' → ', toString($__toTime)),\n concat('bucket ', toString((SELECT max(stamp_inserted) FROM netflow.flows)), ' (+5 min)')) AS period_label\nFROM netflow.flows WHERE (('${period}' = 'range' AND $__timeFilter(stamp_inserted)) OR ('${period}' = 'last5' AND stamp_inserted = (SELECT max(stamp_inserted) FROM netflow.flows))) AND ('${w_ip_src}' = '' OR ip_src = '${w_ip_src}') AND ('${w_ip_dst}' = '' OR ip_dst = '${w_ip_dst}') AND (${w_as_src} = 0 OR as_src = ${w_as_src}) AND (${w_as_dst} = 0 OR as_dst = ${w_as_dst}) AND ('${w_dir}' = 'all' OR direction = '${w_dir}') AND (${w_proto} = 0 OR ip_proto = ${w_proto}) AND (${w_port_src} = 0 OR port_src = ${w_port_src}) AND (${w_port_dst} = 0 OR port_dst = ${w_port_dst}) AND (${w_extra})", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bytes", "decimals": 2, "color": {"mode": "fixed", "fixedColor": "purple"}}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "none", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^total_bytes$/", "values": false}}},
{"id": 4, "title": "Unique src IPs", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 11, "y": 0, "w": 3, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT round(sum(bytes)*8/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e6, 2) AS Mbps, round(sum(packets)/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e3, 2) AS kpps,\n sum(bytes) AS total_bytes, uniq(ip_src) src_ips, uniq(ip_dst) dst_ips, count() AS rows_matched,\n if('${period}'='range', concat(toString($__fromTime), ' → ', toString($__toTime)),\n concat('bucket ', toString((SELECT max(stamp_inserted) FROM netflow.flows)), ' (+5 min)')) AS period_label\nFROM netflow.flows WHERE (('${period}' = 'range' AND $__timeFilter(stamp_inserted)) OR ('${period}' = 'last5' AND stamp_inserted = (SELECT max(stamp_inserted) FROM netflow.flows))) AND ('${w_ip_src}' = '' OR ip_src = '${w_ip_src}') AND ('${w_ip_dst}' = '' OR ip_dst = '${w_ip_dst}') AND (${w_as_src} = 0 OR as_src = ${w_as_src}) AND (${w_as_dst} = 0 OR as_dst = ${w_as_dst}) AND ('${w_dir}' = 'all' OR direction = '${w_dir}') AND (${w_proto} = 0 OR ip_proto = ${w_proto}) AND (${w_port_src} = 0 OR port_src = ${w_port_src}) AND (${w_port_dst} = 0 OR port_dst = ${w_port_dst}) AND (${w_extra})", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "short", "decimals": 0, "color": {"mode": "fixed", "fixedColor": "yellow"}}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "none", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^src_ips$/", "values": false}}},
{"id": 5, "title": "Unique dst IPs", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 14, "y": 0, "w": 3, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT round(sum(bytes)*8/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e6, 2) AS Mbps, round(sum(packets)/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e3, 2) AS kpps,\n sum(bytes) AS total_bytes, uniq(ip_src) src_ips, uniq(ip_dst) dst_ips, count() AS rows_matched,\n if('${period}'='range', concat(toString($__fromTime), ' → ', toString($__toTime)),\n concat('bucket ', toString((SELECT max(stamp_inserted) FROM netflow.flows)), ' (+5 min)')) AS period_label\nFROM netflow.flows WHERE (('${period}' = 'range' AND $__timeFilter(stamp_inserted)) OR ('${period}' = 'last5' AND stamp_inserted = (SELECT max(stamp_inserted) FROM netflow.flows))) AND ('${w_ip_src}' = '' OR ip_src = '${w_ip_src}') AND ('${w_ip_dst}' = '' OR ip_dst = '${w_ip_dst}') AND (${w_as_src} = 0 OR as_src = ${w_as_src}) AND (${w_as_dst} = 0 OR as_dst = ${w_as_dst}) AND ('${w_dir}' = 'all' OR direction = '${w_dir}') AND (${w_proto} = 0 OR ip_proto = ${w_proto}) AND (${w_port_src} = 0 OR port_src = ${w_port_src}) AND (${w_port_dst} = 0 OR port_dst = ${w_port_dst}) AND (${w_extra})", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "short", "decimals": 0, "color": {"mode": "fixed", "fixedColor": "orange"}}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "none", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^dst_ips$/", "values": false}}},
{"id": 6, "title": "Report period", "type": "stat", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 17, "y": 0, "w": 7, "h": 4}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT round(sum(bytes)*8/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e6, 2) AS Mbps, round(sum(packets)/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e3, 2) AS kpps,\n sum(bytes) AS total_bytes, uniq(ip_src) src_ips, uniq(ip_dst) dst_ips, count() AS rows_matched,\n if('${period}'='range', concat(toString($__fromTime), ' → ', toString($__toTime)),\n concat('bucket ', toString((SELECT max(stamp_inserted) FROM netflow.flows)), ' (+5 min)')) AS period_label\nFROM netflow.flows WHERE (('${period}' = 'range' AND $__timeFilter(stamp_inserted)) OR ('${period}' = 'last5' AND stamp_inserted = (SELECT max(stamp_inserted) FROM netflow.flows))) AND ('${w_ip_src}' = '' OR ip_src = '${w_ip_src}') AND ('${w_ip_dst}' = '' OR ip_dst = '${w_ip_dst}') AND (${w_as_src} = 0 OR as_src = ${w_as_src}) AND (${w_as_dst} = 0 OR as_dst = ${w_as_dst}) AND ('${w_dir}' = 'all' OR direction = '${w_dir}') AND (${w_proto} = 0 OR ip_proto = ${w_proto}) AND (${w_port_src} = 0 OR port_src = ${w_port_src}) AND (${w_port_dst} = 0 OR port_dst = ${w_port_dst}) AND (${w_extra})", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "none", "decimals": 2, "color": {"mode": "fixed", "fixedColor": "text"}}, "overrides": []}, "options": {"colorMode": "value", "graphMode": "none", "textMode": "value", "reduceOptions": {"calcs": ["lastNotNull"], "fields": "/^period_label$/", "values": false}}},
{"id": 7, "title": "Flows — columns: ${cols:text} | order by ${order} | limit ${limit}", "type": "table", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 4, "w": 24, "h": 18}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT ${cols:csv},\n round(sum(bytes)*8/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e6, 2) AS Mbps,\n round(sum(packets)/if('${period}' = 'range', dateDiff('second', $__fromTime, $__toTime), 300)/1e3, 2) AS kpps,\n sum(bytes) AS total_bytes, sum(flows) AS flows_cnt\nFROM netflow.flows\nWHERE (('${period}' = 'range' AND $__timeFilter(stamp_inserted)) OR ('${period}' = 'last5' AND stamp_inserted = (SELECT max(stamp_inserted) FROM netflow.flows))) AND ('${w_ip_src}' = '' OR ip_src = '${w_ip_src}') AND ('${w_ip_dst}' = '' OR ip_dst = '${w_ip_dst}') AND (${w_as_src} = 0 OR as_src = ${w_as_src}) AND (${w_as_dst} = 0 OR as_dst = ${w_as_dst}) AND ('${w_dir}' = 'all' OR direction = '${w_dir}') AND (${w_proto} = 0 OR ip_proto = ${w_proto}) AND (${w_port_src} = 0 OR port_src = ${w_port_src}) AND (${w_port_dst} = 0 OR port_dst = ${w_port_dst}) AND (${w_extra})\nGROUP BY ALL\nORDER BY ${order} DESC\nLIMIT ${limit}", "format": 1, "queryType": "table", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"custom": {"align": "auto", "cellOptions": {"type": "auto"}, "filterable": true, "minWidth": 90}}, "overrides": [{"matcher": {"id": "byName", "options": "Mbps"}, "properties": [{"id": "decimals", "value": 2}, {"id": "custom.cellOptions", "value": {"type": "gauge", "mode": "gradient"}}, {"id": "color", "value": {"mode": "continuous-BlPu"}}, {"id": "custom.width", "value": 220}]}, {"matcher": {"id": "byName", "options": "kpps"}, "properties": [{"id": "decimals", "value": 2}, {"id": "custom.width", "value": 110}]}, {"matcher": {"id": "byName", "options": "total_bytes"}, "properties": [{"id": "unit", "value": "bytes"}, {"id": "displayName", "value": "bytes"}, {"id": "custom.width", "value": 110}]}, {"matcher": {"id": "byName", "options": "flows_cnt"}, "properties": [{"id": "displayName", "value": "flows"}, {"id": "custom.width", "value": 90}]}]}, "options": {"showHeader": true, "cellHeight": "sm", "sortBy": [{"displayName": "Mbps", "desc": true}], "footer": {"show": true, "reducer": ["sum"], "fields": ["Mbps", "kpps", "total_bytes", "flows_cnt"], "countRows": false, "enablePagination": false}}},
{"id": 8, "title": "Matched traffic over time (bps, full time range)", "type": "timeseries", "datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "gridPos": {"x": 0, "y": 22, "w": 24, "h": 8}, "targets": [{"datasource": {"type": "grafana-clickhouse-datasource", "uid": "${DS_CLICKHOUSE}"}, "refId": "A", "editorType": "sql", "rawSql": "SELECT $__timeInterval(stamp_inserted) AS t, sum(bytes)*8/$__interval_s AS \"Mbps (matched)\"\nFROM netflow.flows WHERE $__timeFilter(stamp_inserted)\n AND ('${w_ip_src}' = '' OR ip_src = '${w_ip_src}') AND ('${w_ip_dst}' = '' OR ip_dst = '${w_ip_dst}')\n AND (${w_as_src} = 0 OR as_src = ${w_as_src}) AND (${w_as_dst} = 0 OR as_dst = ${w_as_dst})\n AND ('${w_dir}' = 'all' OR direction = '${w_dir}') AND (${w_proto} = 0 OR ip_proto = ${w_proto})\n AND (${w_port_src} = 0 OR port_src = ${w_port_src}) AND (${w_port_dst} = 0 OR port_dst = ${w_port_dst}) AND (${w_extra})\nGROUP BY t ORDER BY t", "format": 0, "queryType": "timeseries", "meta": {}}], "interval": "5m", "fieldConfig": {"defaults": {"unit": "bps", "color": {"mode": "palette-classic"}, "custom": {"drawStyle": "line", "lineWidth": 1, "fillOpacity": 25, "gradientMode": "opacity", "lineInterpolation": "smooth", "showPoints": "never"}}, "overrides": []}, "options": {"legend": {"displayMode": "table", "placement": "bottom", "showLegend": true, "calcs": ["mean", "max", "lastNotNull"]}, "tooltip": {"mode": "multi"}}}
]
}
Variables of the dashboard:
Mode Current = last 5-minute bucket | Average = selected time range
TABLE COLUMNS IP_SRC, IP_DST, PROTO, PORT_SRC, PORT_DST, AS_SRC, AS_DST,
DIRECTION, CUSTOMER_IP, IFACE_IN, IFACE_OUT, EXPORTER
ORDER BY Mbps | kpps | bytes | flows
LIMIT 10 - 500
WHERE IP_SRC / IP_DST exact IP, empty = no filter
WHERE AS_SRC / AS_DST ASN number, 0 = no filter
WHERE PORT_SRC / PORT_DST L4 port, 0 = no filter
WHERE PROTO / DIRECTION dropdown
WHERE extra (SQL) free ClickHouse expression, 1 = no filter
Example: to see which customers receive QUIC traffic from Google, select the columns IP_DST and AS_SRC, set WHERE PROTO to udp, WHERE PORT_SRC to 443 and WHERE AS_SRC to 15169. The generated SQL query of every panel is visible in Inspect -> Query.