Network operations teams face an uncomfortable reality: traditional monitoring tools process data in batches, creating blind spots measured in minutes. During a DDoS attack, those minutes determine whether your service stays online or goes dark. When a port scan precedes a breach, delayed detection means the attacker has already moved laterally through your infrastructure.
The core problem is that network telemetry is inherently a streaming workload. Packets arrive continuously, and the questions you need answered ("Is this a SYN flood?", "Which IP is consuming all our bandwidth?", "Are we seeing abnormal traffic patterns?") require real-time aggregation over that continuous flow. Batch processing turns a real-time problem into a delayed one.
Streaming SQL offers a different approach. Instead of polling databases or waiting for batch jobs to complete, you define materialized views that continuously compute aggregations as data arrives. In this guide, you will build a complete network monitoring system using RisingWave, covering traffic summarization, DDoS detection, bandwidth tracking, port scan identification, and anomaly alerting, all with standard SQL.
Why Streaming SQL for Network Monitoring
Network monitoring has traditionally relied on tools like SNMP polling, NetFlow collectors, and SIEM platforms that ingest logs in periodic intervals. These tools work, but they introduce latency between when an event occurs and when you can act on it. A 5-minute polling interval means a SYN flood can send millions of packets before any alert fires.
A streaming database like RisingWave changes this model fundamentally. Instead of querying accumulated data after the fact, you define the queries upfront as materialized views. RisingWave incrementally maintains these views as each new record arrives. The result is always fresh, with no scheduled jobs, no polling loops, and no stale dashboards.
Here is why this matters for network monitoring specifically:
- Sub-second detection: DDoS patterns, port scans, and bandwidth spikes surface within seconds of the first anomalous packet.
- SQL-native: Your network engineers already know SQL. No need to learn a new DSL or programming framework.
- Incremental computation: RisingWave does not reprocess the entire dataset when new data arrives. It updates only what changed, making it efficient even at high packet volumes.
- Downstream integration: Materialized view results can be sunk to downstream systems like PostgreSQL, Kafka, or alerting platforms for automated response.
Setting Up the Network Packet Stream
Every network monitoring system starts with a data source. In production, you would typically ingest NetFlow/sFlow records, packet captures, or firewall logs from a message broker like Apache Kafka. For this tutorial, we use a RisingWave table to simulate that stream. The schema maps directly to common NetFlow v5/v9 fields.
CREATE TABLE network_packets (
packet_id BIGINT,
event_time TIMESTAMPTZ,
src_ip VARCHAR,
dst_ip VARCHAR,
src_port INT,
dst_port INT,
protocol VARCHAR,
bytes_transferred BIGINT,
packet_count INT,
tcp_flags VARCHAR,
interface_id VARCHAR
);
In a production deployment, you would replace this with a Kafka source using RisingWave's CREATE SOURCE syntax, pointing to the topic where your network appliances publish telemetry data.
Let's load sample data that simulates a realistic mix of normal traffic and suspicious activity:
INSERT INTO network_packets VALUES
(1, '2026-04-01 10:00:01+00', '192.168.1.100', '10.0.0.5', 54321, 443, 'TCP', 1500, 1, 'SYN', 'eth0'),
(2, '2026-04-01 10:00:01+00', '192.168.1.101', '10.0.0.5', 54322, 443, 'TCP', 1200, 1, 'SYN', 'eth0'),
(3, '2026-04-01 10:00:02+00', '192.168.1.100', '10.0.0.5', 54321, 443, 'TCP', 52000, 35, 'ACK', 'eth0'),
(4, '2026-04-01 10:00:02+00', '203.0.113.50', '10.0.0.5', 12345, 80, 'TCP', 64, 1, 'SYN', 'eth1'),
(5, '2026-04-01 10:00:02+00', '203.0.113.51', '10.0.0.5', 12346, 80, 'TCP', 64, 1, 'SYN', 'eth1'),
(6, '2026-04-01 10:00:03+00', '203.0.113.52', '10.0.0.5', 12347, 80, 'TCP', 64, 1, 'SYN', 'eth1'),
(7, '2026-04-01 10:00:03+00', '203.0.113.53', '10.0.0.5', 12348, 80, 'TCP', 64, 1, 'SYN', 'eth1'),
(8, '2026-04-01 10:00:03+00', '203.0.113.54', '10.0.0.5', 12349, 80, 'TCP', 64, 1, 'SYN', 'eth1'),
(9, '2026-04-01 10:00:04+00', '10.0.0.10', '10.0.0.20', 8080, 5432, 'TCP', 320000, 220, 'ACK', 'eth0'),
(10, '2026-04-01 10:00:04+00', '172.16.0.5', '10.0.0.30', 9090, 8443, 'TCP', 45000, 30, 'PSH', 'eth2'),
(11, '2026-04-01 10:00:05+00', '203.0.113.55', '10.0.0.5', 12350, 80, 'TCP', 64, 1, 'SYN', 'eth1'),
(12, '2026-04-01 10:00:05+00', '203.0.113.56', '10.0.0.5', 12351, 80, 'TCP', 64, 1, 'SYN', 'eth1'),
(13, '2026-04-01 10:00:05+00', '192.168.1.100', '10.0.0.5', 54321, 443, 'TCP', 48000, 32, 'ACK', 'eth0'),
(14, '2026-04-01 10:00:06+00', '10.0.0.10', '10.0.0.20', 8080, 5432, 'TCP', 280000, 190, 'ACK', 'eth0'),
(15, '2026-04-01 10:00:06+00', '192.168.1.102', '10.0.0.40', 60000, 53, 'UDP', 512, 4, '', 'eth0');
This dataset contains normal HTTPS traffic (rows 1-3, 13), a SYN flood from the 203.0.113.0/24 range targeting port 80 (rows 4-8, 11-12), heavy internal database traffic (rows 9, 14), and standard DNS queries (row 15).
Monitoring Traffic Patterns with Materialized Views
Traffic Summary by Interface and Protocol
The first view every network team needs is a high-level summary: how much traffic is flowing through each interface, broken down by protocol. This serves as the foundation for capacity planning and baseline establishment.
CREATE MATERIALIZED VIEW traffic_summary AS
SELECT
interface_id,
protocol,
COUNT(*) AS total_packets,
SUM(bytes_transferred) AS total_bytes,
SUM(packet_count) AS total_packet_count,
AVG(bytes_transferred) AS avg_bytes_per_record
FROM network_packets
GROUP BY interface_id, protocol;
Query the view to see current traffic distribution:
SELECT * FROM traffic_summary ORDER BY total_bytes DESC;
interface_id | protocol | total_packets | total_bytes | total_packet_count | avg_bytes_per_record
--------------+----------+---------------+-------------+--------------------+--------------------------------
eth0 | TCP | 6 | 702700 | 479 | 117116.66666666666666666666667
eth2 | TCP | 1 | 45000 | 30 | 45000
eth0 | UDP | 1 | 512 | 4 | 512
eth1 | TCP | 7 | 448 | 7 | 64
Notice eth1 immediately: 7 TCP records but only 448 bytes total (64 bytes average). That pattern - many connections with minimal payload - is characteristic of a SYN flood. The eth0 interface carries the bulk of legitimate traffic at ~702 KB.
Identifying Top Talkers
Knowing which IP addresses generate the most traffic is essential for both capacity planning and security investigations. The top_talkers view continuously ranks source IPs by volume:
CREATE MATERIALIZED VIEW top_talkers AS
SELECT
src_ip,
COUNT(*) AS connection_count,
SUM(bytes_transferred) AS total_bytes,
SUM(packet_count) AS total_packets,
COUNT(DISTINCT dst_ip) AS unique_destinations,
COUNT(DISTINCT dst_port) AS unique_dst_ports
FROM network_packets
GROUP BY src_ip;
SELECT * FROM top_talkers ORDER BY total_bytes DESC LIMIT 5;
src_ip | connection_count | total_bytes | total_packets | unique_destinations | unique_dst_ports
---------------+------------------+-------------+---------------+---------------------+------------------
10.0.0.10 | 2 | 600000 | 410 | 1 | 1
192.168.1.100 | 3 | 101500 | 68 | 1 | 1
172.16.0.5 | 1 | 45000 | 30 | 1 | 1
192.168.1.101 | 1 | 1200 | 1 | 1 | 1
192.168.1.102 | 1 | 512 | 4 | 1 | 1
The 10.0.0.10 host is pushing 600 KB to a single destination on a single port (5432 - PostgreSQL). That is likely a database replication stream or a large query result. Whether it is concerning depends on whether that traffic is expected. The unique_destinations and unique_dst_ports columns help distinguish legitimate high-volume users from potentially compromised hosts that spray traffic across many targets.
Detecting DDoS Attacks
SYN Flood Detection
A SYN flood is one of the most common DDoS attack types. The attacker sends TCP SYN packets from many different source IPs (often spoofed) to a target, exhausting the server's connection table. The telltale signature: many unique source IPs sending SYN packets to the same destination IP and port.
CREATE MATERIALIZED VIEW ddos_alerts AS
SELECT
dst_ip,
dst_port,
interface_id,
COUNT(DISTINCT src_ip) AS unique_sources,
COUNT(*) AS syn_count,
SUM(bytes_transferred) AS total_bytes
FROM network_packets
WHERE tcp_flags = 'SYN'
GROUP BY dst_ip, dst_port, interface_id
HAVING COUNT(DISTINCT src_ip) >= 3;
The HAVING clause is where you set your detection threshold. Here, 3 unique sources triggering SYN packets to the same destination fires the alert. In production, you would tune this based on your traffic baseline, likely setting it to hundreds or thousands.
SELECT * FROM ddos_alerts;
dst_ip | dst_port | interface_id | unique_sources | syn_count | total_bytes
----------+----------+--------------+----------------+-----------+-------------
10.0.0.5 | 80 | eth1 | 7 | 7 | 448
Seven unique source IPs, all targeting 10.0.0.5:80 on the eth1 interface, each sending only a 64-byte SYN packet. This is a textbook SYN flood signature.
Incomplete Handshake Detection
SYN floods leave another fingerprint: incomplete TCP handshakes. Legitimate connections follow the SYN -> SYN-ACK -> ACK pattern. Attack traffic typically sends SYN packets and never completes the handshake. This view tracks connection state per source-destination pair:
CREATE MATERIALIZED VIEW connection_flood_detection AS
SELECT
src_ip,
dst_ip,
dst_port,
COUNT(*) AS connection_attempts,
SUM(CASE WHEN tcp_flags = 'SYN' THEN 1 ELSE 0 END) AS syn_packets,
SUM(CASE WHEN tcp_flags = 'ACK' THEN 1 ELSE 0 END) AS ack_packets,
CASE
WHEN SUM(CASE WHEN tcp_flags = 'SYN' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN tcp_flags = 'ACK' THEN 1 ELSE 0 END) = 0
THEN 'INCOMPLETE_HANDSHAKE'
ELSE 'NORMAL'
END AS connection_status
FROM network_packets
WHERE protocol = 'TCP'
GROUP BY src_ip, dst_ip, dst_port;
Filter for suspicious connections:
SELECT * FROM connection_flood_detection
WHERE connection_status = 'INCOMPLETE_HANDSHAKE'
ORDER BY syn_packets DESC LIMIT 5;
src_ip | dst_ip | dst_port | connection_attempts | syn_packets | ack_packets | connection_status
---------------+----------+----------+---------------------+-------------+-------------+----------------------
203.0.113.50 | 10.0.0.5 | 80 | 1 | 1 | 0 | INCOMPLETE_HANDSHAKE
203.0.113.51 | 10.0.0.5 | 80 | 1 | 1 | 0 | INCOMPLETE_HANDSHAKE
203.0.113.55 | 10.0.0.5 | 80 | 1 | 1 | 0 | INCOMPLETE_HANDSHAKE
192.168.1.101 | 10.0.0.5 | 443 | 1 | 1 | 0 | INCOMPLETE_HANDSHAKE
203.0.113.56 | 10.0.0.5 | 80 | 1 | 1 | 0 | INCOMPLETE_HANDSHAKE
Every IP from the 203.0.113.0/24 range shows SYN-only traffic with zero ACK packets - confirmed attack pattern. The 192.168.1.101 entry on port 443 is likely a legitimate connection that simply has not completed the handshake yet. Combining this view with the ddos_alerts view gives you both aggregate and per-source visibility.
Tracking Bandwidth Utilization
Network capacity planning depends on knowing exactly how much bandwidth each interface consumes. This materialized view provides a continuously updated bandwidth dashboard:
CREATE MATERIALIZED VIEW bandwidth_utilization AS
SELECT
interface_id,
COUNT(*) AS record_count,
SUM(bytes_transferred) AS total_bytes,
SUM(bytes_transferred) / (1024.0 * 1024.0) AS total_mb,
SUM(packet_count) AS total_packets,
MAX(bytes_transferred) AS max_single_transfer
FROM network_packets
GROUP BY interface_id;
SELECT * FROM bandwidth_utilization ORDER BY total_bytes DESC;
interface_id | record_count | total_bytes | total_mb | total_packets | max_single_transfer
--------------+--------------+-------------+----------------------+---------------+---------------------
eth0 | 7 | 703212 | 0.670635223388671875 | 483 | 320000
eth2 | 1 | 45000 | 0.042915344238281250 | 30 | 45000
eth1 | 7 | 448 | 0.00042724609375 | 7 | 64
The max_single_transfer column is particularly useful. A single 320 KB transfer on eth0 warrants investigation - is this a large query result, a file transfer, or data exfiltration? Cross-referencing this with the top_talkers view reveals the source.
In production, you would layer time-windowed views on top of this to track bandwidth trends over 1-minute, 5-minute, and 1-hour windows, establishing baselines that make anomaly detection more precise.
Alerting on Anomalies
Volume-Based Anomaly Detection
Anomaly detection does not require machine learning for the most common cases. Threshold-based classification, when applied to continuously maintained aggregations, catches the majority of network issues:
CREATE MATERIALIZED VIEW traffic_anomalies AS
SELECT
src_ip,
dst_ip,
dst_port,
protocol,
interface_id,
SUM(bytes_transferred) AS total_bytes,
SUM(packet_count) AS total_packets,
CASE
WHEN SUM(bytes_transferred) > 200000 THEN 'CRITICAL'
WHEN SUM(bytes_transferred) > 100000 THEN 'WARNING'
ELSE 'NORMAL'
END AS severity
FROM network_packets
GROUP BY src_ip, dst_ip, dst_port, protocol, interface_id
HAVING SUM(bytes_transferred) > 100000;
SELECT * FROM traffic_anomalies ORDER BY total_bytes DESC;
src_ip | dst_ip | dst_port | protocol | interface_id | total_bytes | total_packets | severity
---------------+-----------+----------+----------+--------------+-------------+---------------+----------
10.0.0.10 | 10.0.0.20 | 5432 | TCP | eth0 | 600000 | 410 | CRITICAL
192.168.1.100 | 10.0.0.5 | 443 | TCP | eth0 | 101500 | 68 | WARNING
Two anomalies surfaced. The CRITICAL alert on the 10.0.0.10 -> 10.0.0.20 path (port 5432) shows 600 KB of database traffic, likely a large result set or replication stream. The WARNING on 192.168.1.100 -> 10.0.0.5 (port 443) is HTTPS traffic exceeding 100 KB.
To deliver these alerts to your incident response workflow, you can create a sink from this materialized view to a Kafka topic, which then feeds into PagerDuty, Slack, or your SIEM.
Port Scan Detection
Port scanning is a reconnaissance technique where an attacker probes multiple ports on a target to discover running services. The signature: a single source IP connecting to many different destination ports on the same target.
CREATE MATERIALIZED VIEW port_scan_detection AS
SELECT
src_ip,
dst_ip,
COUNT(DISTINCT dst_port) AS unique_ports_targeted,
COUNT(*) AS total_attempts,
SUM(CASE WHEN tcp_flags = 'SYN' THEN 1 ELSE 0 END) AS syn_only_count
FROM network_packets
WHERE protocol = 'TCP'
GROUP BY src_ip, dst_ip
HAVING COUNT(DISTINCT dst_port) >= 2;
After inserting data that simulates a port scan:
INSERT INTO network_packets VALUES
(16, '2026-04-01 10:00:07+00', '198.51.100.10', '10.0.0.5', 40000, 22, 'TCP', 64, 1, 'SYN', 'eth1'),
(17, '2026-04-01 10:00:07+00', '198.51.100.10', '10.0.0.5', 40001, 23, 'TCP', 64, 1, 'SYN', 'eth1'),
(18, '2026-04-01 10:00:07+00', '198.51.100.10', '10.0.0.5', 40002, 80, 'TCP', 64, 1, 'SYN', 'eth1'),
(19, '2026-04-01 10:00:08+00', '198.51.100.10', '10.0.0.5', 40003, 443, 'TCP', 64, 1, 'SYN', 'eth1'),
(20, '2026-04-01 10:00:08+00', '198.51.100.10', '10.0.0.5', 40004, 3389, 'TCP', 64, 1, 'SYN', 'eth1');
The materialized view automatically updates:
SELECT * FROM port_scan_detection;
src_ip | dst_ip | unique_ports_targeted | total_attempts | syn_only_count
---------------+----------+-----------------------+----------------+----------------
198.51.100.10 | 10.0.0.5 | 5 | 5 | 5
IP 198.51.100.10 probed 5 different ports (22, 23, 80, 443, 3389) on 10.0.0.5, all SYN-only. This is a classic horizontal port scan targeting common services: SSH, Telnet, HTTP, HTTPS, and RDP.
From Detection to Response: Sinking Alerts
Detecting threats is only useful if the alerts reach the right people. RisingWave can push materialized view changes to external systems through sinks. Here is how you would route DDoS alerts to a Kafka topic for downstream consumption by your SIEM or incident response platform:
CREATE SINK ddos_alerts_sink FROM ddos_alerts
WITH (
connector = 'kafka',
properties.bootstrap.server = 'kafka:9092',
topic = 'network.ddos.alerts',
type = 'upsert',
primary_key = 'dst_ip,dst_port,interface_id'
);
Every time the ddos_alerts materialized view updates (a new SYN flood pattern is detected, or an existing one intensifies), the change is pushed to the network.ddos.alerts Kafka topic. From there, a consumer can trigger automated responses: updating firewall rules, sending Slack notifications, or opening incident tickets.
This pattern - streaming SQL for detection, Kafka for event routing, and downstream tools for response - gives you a fully automated security pipeline with no batch delays.
Architecture Overview
The complete architecture for a production network monitoring system built on RisingWave looks like this:
graph LR
A[Network Devices] -->|NetFlow/sFlow| B[Kafka]
B -->|Stream Ingestion| C[RisingWave]
C -->|Materialized Views| D[Traffic Summary]
C -->|Materialized Views| E[DDoS Detection]
C -->|Materialized Views| F[Anomaly Alerts]
C -->|Materialized Views| G[Port Scan Detection]
E -->|Sink| H[Kafka Alert Topic]
F -->|Sink| H
G -->|Sink| H
H -->|Consumer| I[PagerDuty / Slack]
D -->|Sink| J[Grafana / Dashboard]
Network devices export telemetry to Kafka. RisingWave ingests from Kafka and maintains multiple materialized views, each focused on a specific monitoring dimension. Detection views sink their results back to Kafka alert topics, which feed automated response systems. Summary views sink to a PostgreSQL-compatible endpoint for Grafana dashboards.
What are the benefits of using streaming SQL for network monitoring?
Streaming SQL provides three key advantages over traditional network monitoring approaches. First, it eliminates polling delays by computing results incrementally as data arrives, giving you sub-second detection of threats like DDoS attacks and port scans. Second, it uses standard SQL syntax, which means network engineers can write and modify detection rules without learning specialized programming languages. Third, materialized views in RisingWave are incrementally maintained, so even at high packet volumes (millions of records per second), the system processes only the delta rather than rescanning all historical data.
How does RisingWave detect DDoS attacks in real time?
RisingWave detects DDoS attacks through materialized views that continuously aggregate packet-level data. For SYN flood detection, a view counts the number of unique source IPs sending SYN packets to the same destination IP and port. When this count exceeds a configurable threshold, the attack is flagged. Because the view updates incrementally with each new packet, detection occurs within seconds of the attack starting, not minutes later when a batch job runs.
Can streaming SQL replace traditional SIEM tools?
Streaming SQL complements rather than fully replaces a SIEM. It excels at real-time detection and aggregation, handling the "detect fast" part of the security workflow. Traditional SIEMs provide forensic investigation capabilities, compliance reporting, and long-term log storage that a streaming database is not designed for. The recommended architecture uses streaming SQL for real-time detection and alerting, then sinks events to a SIEM for investigation and compliance. This hybrid approach gives you both speed and depth.
What network data sources work with RisingWave?
RisingWave can ingest network telemetry from any source that produces structured data. Common integrations include NetFlow/sFlow/IPFIX records published to Apache Kafka topics, syslog feeds from firewalls and routers, packet capture (PCAP) data parsed and published by tools like Zeek or Suricata, and cloud VPC flow logs from AWS, GCP, or Azure. RisingWave supports Kafka, Pulsar, Kinesis, and other connectors as source inputs, making it compatible with most existing network telemetry pipelines.
Conclusion
Building a real-time network monitoring system does not require specialized stream processing frameworks or complex event processing engines. With streaming SQL and materialized views, you can:
- Detect SYN floods and DDoS patterns by aggregating TCP flags across source IPs in real time
- Track bandwidth utilization per interface with continuously updated byte and packet counters
- Identify port scans by monitoring the number of unique destination ports per source-destination pair
- Classify traffic anomalies using threshold-based severity levels that update with every new packet
- Automate incident response by sinking detection results to Kafka for downstream consumption
Every SQL example in this article was verified against RisingWave 2.8.0. The materialized views update incrementally, meaning the system scales with packet volume without requiring periodic full recomputation.
Ready to build your own network monitoring pipeline? Try RisingWave Cloud free - no credit card required. Sign up here.
Join our Slack community to ask questions and connect with other stream processing developers.
