Sổ tay kỹ thuật

ClickHouse – query top talkers

cheatsheet#clickhouse#akvorado#netflow· cập nhật 2026-11-04

Query mẫu lấy top IP/AS/interface theo băng thông từ bảng flows của Akvorado.

Sửa trang này
Mục lục

Bảng mặc định của Akvorado là flows (đơn vị Bytes đã nhân sampling). Đổi khoảng thời gian bằng INTERVAL.

Top talkers

Mục đích Query
Top 10 IP nguồn (1 giờ) SELECT SrcAddr, formatReadableSize(sum(Bytes)) AS vol FROM flows WHERE TimeReceived > now() - INTERVAL 1 HOUR GROUP BY SrcAddr ORDER BY sum(Bytes) DESC LIMIT 10
Top AS đích SELECT DstAS, dictGetOrDefault('asns','name',DstAS,'?') AS name, sum(Bytes)*8/3600 AS bps FROM flows WHERE TimeReceived > now() - INTERVAL 1 HOUR GROUP BY DstAS ORDER BY bps DESC LIMIT 10
Top interface vào SELECT ExporterName, InIfName, sum(Bytes) AS b FROM flows WHERE TimeReceived > now() - INTERVAL 15 MINUTE GROUP BY 1,2 ORDER BY b DESC LIMIT 20
Top cặp src→dst SELECT SrcAddr, DstAddr, sum(Bytes) b FROM flows WHERE TimeReceived > now() - INTERVAL 5 MINUTE GROUP BY 1,2 ORDER BY b DESC LIMIT 20
Theo protocol/port SELECT Proto, DstPort, sum(Packets) p FROM flows WHERE TimeReceived > now() - INTERVAL 1 HOUR GROUP BY 1,2 ORDER BY p DESC LIMIT 20

Time-series cho Grafana

SELECT
  toStartOfInterval(TimeReceived, INTERVAL 1 MINUTE) AS t,
  SrcAS,
  sum(Bytes) * 8 / 60 AS bps
FROM flows
WHERE $__timeFilter(TimeReceived)
  AND SrcAS IN (
    SELECT SrcAS FROM flows
    WHERE $__timeFilter(TimeReceived)
    GROUP BY SrcAS ORDER BY sum(Bytes) DESC LIMIT 5
  )
GROUP BY t, SrcAS
ORDER BY t

Lệnh vận hành

Mục đích Lệnh
Dung lượng theo bảng SELECT table, formatReadableSize(sum(bytes_on_disk)) FROM system.parts WHERE active GROUP BY table ORDER BY sum(bytes_on_disk) DESC
Query đang chạy SELECT query_id, elapsed, query FROM system.processes
Kill query KILL QUERY WHERE query_id = '<id>'
Mở client clickhouse-client --host ch.example.net --secure