ntopng ClickHouse Database Schema

This document describes the ClickHouse database schema used by ntopng to persist flow records, alerts, assets, and related analytical data. The schema is defined in two files that must be kept in sync:

Both files are parsed and executed by ntopng at startup via pro/src/ClickHouseDB.cpp. Statements are separated by @ delimiters rather than standard semicolons to allow ntopng's SQL runner to feed them one at a time. Each file contains both CREATE TABLE IF NOT EXISTS statements (idempotent schema bootstrap) and ALTER TABLE … ADD/MODIFY COLUMN IF NOT EXISTS statements (idempotent schema migrations for upgrades from older versions).

Table Overview

Views that merge historical and engaged tables are described in the Views section.

flows

The central table. Every bidirectional network flow observed by ntopng — whether captured locally or received via NetFlow/sFlow/IPFIX — produces one row. Partitioned by day on FIRST_SEEN; ordered by (FIRST_SEEN, IPV4_SRC_ADDR, IPV4_DST_ADDR).

Key column groups

Note on alert flows: A flow row doubles as an alert record when STATUS != 0. The flow_alerts_view filters for these rows and joins them with mitre_table_info for ATT&CK enrichment.

Country encoding: SRC_COUNTRY_CODE and DST_COUNTRY_CODE store two ISO 3166-1 ASCII letters packed into a UInt16 (high byte = first letter). The flow_alerts_view decodes them with char(bitShiftRight(..., 8), bitAnd(..., 0xFF)).

hourly_flows

Hourly rollup of the flows table. Multiple raw flows sharing the same 5-tuple within an hour are collapsed into one row with summed byte/packet counters. Used for long-term trend analysis and dashboards that do not need per-flow granularity.

Compared to flows it omits per-flow alert fields, process info, fingerprints, TCP flags, and NAT columns, and adds:

• NUM_FLOWS UInt32 — number of raw flows collapsed into this row.

hourly_asn

Hourly aggregated traffic statistics broken down by source/destination ASN pair. Used for AS-level traffic analysis and BGP peer analytics. Partitioned by day on FIRST_SEEN; ordered by (FIRST_SEEN, SRC_ASN, DST_ASN).

Columns include SRC_ASN, DST_ASN, SRC_PEER_ASN, DST_PEER_ASN, directional byte/packet counters, PROBE_IP, and SNMP interface indices.

Alert Tables

ntopng models nine entity types that can generate alerts. Each entity type has a pair of tables:

Common columns (all alert tables)

Entity-specific columns

active_monitoring_alerts: resolved_ip, resolved_name, measurement, measure_threshold, measure_value

host_alerts: ip_version, ip, vlan_id, name, is_attacker, is_victim, is_client, is_server, host_pool_id, network, country

mac_alerts: address, device_type, name, is_attacker, is_victim

snmp_alerts: ip, port (ifIndex), name, port_name

network_alerts: local_network_id, name, alias

as_alerts: asn, name, alias

interface_alerts: ifid, subtype, name, alias

user_alerts: user

system_alerts: name

Engaged Alert Tables

Each engaged_*_alerts table uses the Memory engine and holds only the currently-firing (engaged) alerts. Rows are inserted when an alert fires and deleted when it resolves. They have identical column schemas to their MergeTree counterparts.

These tables are dropped and recreated on every ntopng startup to prevent stale engaged alerts from persisting across restarts.

vulnerability_scan_data

Stores raw per-host vulnerability scan results produced by the ntopng Vulnerability Scanner (VS) module. Partitioned by day on LAST_SCAN; ordered by (LAST_SCAN, HOST, SCAN_TYPE).

vulnerability_scan_report

One row per completed vulnerability scan report. Partitioned by day on REPORT_DATE.

mitre_table_info

Maps ntopng alert type IDs and entity type IDs to MITRE ATT&CK framework entries. Uses ReplacingMergeTree keyed on (ALERT_ID, ENTITY_ID) to prevent duplicates.

This table is joined by host_alerts_view and flow_alerts_view to surface MITRE context alongside alert data.

assets

Network asset inventory. One row per discovered or imported asset (host, MAC address, network device). Uses ReplacingMergeTree(version) so that re-observations update existing rows rather than creating duplicates. Primary key is (type, key).

Views

Views are recreated on each ntopng startup (DROP VIEW IF EXISTS + CREATE VIEW IF NOT EXISTS).

Per-entity alert views

Each *_alerts_view is a UNION ALL of its historical MergeTree table and its in-memory engaged counterpart, providing a unified query surface for both resolved and currently-active alerts:

-- example
SELECT * FROM host_alerts
UNION ALL
SELECT * FROM engaged_host_alerts

host_alerts_view additionally LEFT JOINs mitre_table_info (on ENTITY_ID = 1) to attach ATT&CK tactic/technique columns.

flow_alerts_view

Selects only alert flows from the flows table (STATUS != 0 AND IS_ALERT_DELETED != 1), renames columns to a friendlier lowercase schema (e.g. IPV4_SRC_ADDR → resolved cli_ip string), and LEFT JOINs mitre_table_info (on ENTITY_ID = 4).

all_alerts_view

A single UNION ALL across all per-entity alert views plus alert flows, exposing a minimal common schema: entity_id, interface_id, alert_id, alert_status, require_attention, tstamp, tstamp_end, severity, score, alert_category. Entity ID values:

Schema Versioning and Migrations

The schema files follow an additive migration pattern: new columns are added with ALTER TABLE … ADD COLUMN IF NOT EXISTS statements appended at the end of the file. Columns are never removed via migration (only via explicit DROP COLUMN IF EXISTS for columns that were renamed or replaced). This ensures forward compatibility — an older ntopng version can read a database created by a newer version without crashing, it will simply ignore unknown columns.

When adding a new column to the schema:

1. Add it to the CREATE TABLE statement.
2. Add a corresponding ALTER TABLE … ADD COLUMN IF NOT EXISTS migration statement.
3. Keep db_schema_clickhouse.sql and db_schema_clickhouse_cluster.sql in sync.
4. Also update pro/include/FlowsTable.h, pro/src/ClickHouseDB.cpp, scripts/lua/modules/tag_utils.lua, and scripts/lua/modules/historical_flow_utils.lua as noted in the header comment of the schema files.