docs/querying.md

Querying the Database

Each dataset has its own SQLite database at the dataset's configured db_path.

bash

1	sqlite3 data/<dataset>/netflow.sqlite

Useful SQLite commands:

text

1	.tables
2	.schema traffic_stats
3	.schema protocol_stats
4	.schema address_count_stats
5	.schema address_structure_stats

traffic_stats — per-source flow/packet/byte counts:

protocol_stats — unique protocol counts per source/time bucket.

address_count_stats — unique source/destination address counts per source/time bucket.

address_structure_stats — MAAD-backed address structure rows per source/time bucket.

granularity is one of 5m, 30m, 1h, 1d.

Daily flow/packet summary for a time window:

sql

1	SELECT
2	DATE(bucket_start, 'unixepoch') AS day,
3	source_id,
4	SUM(flows) AS flows,
5	SUM(packets) AS packets,
6	SUM(bytes) AS bytes
7	FROM traffic_stats
8	WHERE source_id IN ('source1', 'source2')
9	AND granularity = '1d'
10	AND src_visibility = 'all'
11	AND dst_visibility = 'all'
12	AND bucket_start BETWEEN strftime('%s', '2025-01-01') AND strftime('%s', '2025-01-08')
13	GROUP BY day, source_id
14	ORDER BY day, source_id;

30-minute protocol breakdown for a single day:

sql

1	SELECT
2	datetime(bucket_start, 'unixepoch') AS bucket,
3	protocol,
4	protocol_count
5	FROM protocol_stats
6	WHERE source_id = 'source1'
7	AND granularity = '30m'
8	AND ip_version = 4
9	AND src_visibility = 'all'
10	AND dst_visibility = 'all'
11	AND bucket_start BETWEEN strftime('%s', '2025-01-03') AND strftime('%s', '2025-01-04')
12	ORDER BY bucket_start, protocol;

Per-source address counts:

sql

1	SELECT source_id, bucket_start, ip_version, address_side, address_count
2	FROM address_count_stats
3	WHERE granularity = '1h'
4	AND source_id IN ('source1', 'source2')
5	AND src_visibility = 'all'
6	AND dst_visibility = 'all'
7	AND bucket_start BETWEEN strftime('%s', '2025-01-01') AND strftime('%s', '2025-01-02')
8	ORDER BY source_id, bucket_start, ip_version, address_side;