docs/querying.md
Querying the Database
Each dataset has its own SQLite database at data/<dataset>/netflow.sqlite.
Direct Access
bash
| 1 | sqlite3 data/<dataset>/netflow.sqlite |
Useful SQLite commands:
text
| 1 | .tables |
| 2 | .schema netflow_stats |
| 3 | .schema ip_stats |
Schema Overview
netflow_stats — per-router flow/packet/byte counts, bucketed by file timestamp:
file_path,router,timestamp(Unix timestamp)flows,flows_tcp,flows_udp,flows_icmp,flows_otherpackets,packets_tcp,packets_udp,packets_icmp,packets_otherbytes,bytes_tcp,bytes_udp,bytes_icmp,bytes_otherfirst_timestamp,last_timestamp,msec_first,msec_last- Indexed for efficient router/time-range queries
ip_stats — unique source/destination IP counts per router per time bucket:
router,granularity,bucket_start,bucket_endgranularityis one of5m,30m,1h,1dsa_ipv4_count,da_ipv4_count,sa_ipv6_count,da_ipv6_count
Example Queries
Daily flow/packet summary for a time window:
sql
| 1 | SELECT |
| 2 | DATE(timestamp, 'unixepoch') AS day, |
| 3 | router, |
| 4 | SUM(flows) AS flows, |
| 5 | SUM(packets) AS packets, |
| 6 | SUM(bytes) AS bytes |
| 7 | FROM netflow_stats |
| 8 | WHERE router IN ('router1', 'router2') |
| 9 | AND timestamp BETWEEN strftime('%s', '2025-01-01') AND strftime('%s', '2025-01-08') |
| 10 | GROUP BY day, router |
| 11 | ORDER BY day, router; |
30-minute protocol breakdown for a single day:
sql
| 1 | SELECT |
| 2 | strftime('%Y-%m-%d %H:%M', timestamp - (timestamp % 1800), 'unixepoch') AS bucket, |
| 3 | SUM(flows_tcp) AS flows_tcp, |
| 4 | SUM(flows_udp) AS flows_udp, |
| 5 | SUM(flows_icmp) AS flows_icmp |
| 6 | FROM netflow_stats |
| 7 | WHERE router = 'router1' |
| 8 | AND timestamp BETWEEN strftime('%s', '2025-01-03') AND strftime('%s', '2025-01-04') |
| 9 | GROUP BY timestamp - (timestamp % 1800) |
| 10 | ORDER BY timestamp - (timestamp % 1800); |
Per-router IP counts:
sql
| 1 | SELECT router, bucket_start, sa_ipv4_count, da_ipv4_count, sa_ipv6_count, da_ipv6_count |
| 2 | FROM ip_stats |
| 3 | WHERE granularity = '1h' |
| 4 | AND router IN ('router1', 'router2') |
| 5 | AND bucket_start BETWEEN strftime('%s', '2025-01-01') AND strftime('%s', '2025-01-02') |
| 6 | ORDER BY router, bucket_start; |