Real-Time Network Monitoring for Telecoms with Streaming SQL: Performance, Capacity, 5G Slicing, and Subscriber Experience

Real-Time Network Monitoring for Telecoms with Streaming SQL: Performance, Capacity, 5G Slicing, and Subscriber Experience

·

Updated

·

19 min read

Real-time network monitoring with streaming SQL means packet and flow records, per-cell performance counters, backhaul utilization, 5G slice QoS metrics, and per-subscriber measurement reports are aggregated as they arrive, so a SYN flood, a port scan, a congested cell, a saturated backhaul link, an URLLC latency breach, or a subscriber about to churn surfaces within seconds instead of at the next 15-minute performance management file. This guide builds the network data layer on RisingWave, a PostgreSQL-compatible streaming database, starting with a verified packet-level tutorial and extending it to radio access network KPIs, capacity planning, network slicing, and subscriber quality of experience.

Why Network Telemetry Belongs in a Streaming Database

Network telemetry is inherently a streaming workload. Packets arrive continuously, counters tick every second, and the questions operators ask, "Is this a SYN flood?", "Which cell is saturating?", "Is the factory slice inside its latency SLA?", "Which subscriber has had three dropped sessions this hour?", are aggregations over that continuous flow. Traditional tooling answers them late: SNMP polling and NetFlow collectors introduce minutes of delay, performance management systems export counter files every 15 minutes, and capacity planning runs on weekly reports that cannot see a stadium event or a viral surge between runs. During a DDoS attack or a degraded cell site serving thousands of subscribers, those minutes are the difference between staying online and going dark.

RisingWave inverts the model. Instead of querying accumulated data after the fact, engineers define the queries up front as materialized views, and RisingWave maintains them incrementally as records arrive, updating only what changed. Network engineers already know SQL, so there is no new DSL or stream-processing framework. Results are queryable over the PostgreSQL protocol by Grafana and NOC dashboards and pushed through sinks to Kafka, PostgreSQL, PagerDuty, ticketing, and retention systems for automated response. RisingWave is open source under Apache 2.0, written in Rust, and checkpoints state to S3, so the detection state survives restarts.

Tutorial: Packet Streams, Top Talkers, SYN Floods, and Port Scans

Start with a table standing in for the Kafka source; in production, NetFlow, sFlow, IPFIX, Zeek or Suricata output, firewall syslog, or cloud VPC flow logs land in a topic and CREATE SOURCE replaces the table with no change to the views.

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
);

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');

The sample mixes normal HTTPS (rows 1 to 3 and 13), a SYN flood from the 203.0.113.0/24 range against port 80 (rows 4 to 8, 11, 12), heavy internal database traffic (rows 9, 14), and a DNS query (row 15). The first view every team needs is traffic per interface and protocol, and the second is which sources generate the most of it.

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;

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 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.666666666667
 eth2         | TCP      |             1 |       45000 |                 30 |                45000
 eth0         | UDP      |             1 |         512 |                  4 |                  512
 eth1         | TCP      |             7 |         448 |                  7 |                   64

The eth1 row stands out immediately: seven TCP records and 448 bytes total, 64 bytes each. Many connections with minimal payload is the shape of a SYN flood. In top_talkers, 10.0.0.10 pushes 600 KB to one destination on port 5432, which is a replication stream or a large result set, not an attack. The SYN flood itself is a count of distinct sources sending SYN to one destination, and the incomplete-handshake view confirms it by showing SYN with no ACK.

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;

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;
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 sources, all targeting 10.0.0.5:80 on eth1, each a 64-byte SYN. Every 203.0.113.x source shows INCOMPLETE_HANDSHAKE; the single 192.168.1.101 entry on 443 is a legitimate connection that has not finished its handshake yet, which is why the threshold is on distinct sources, not on any one incomplete handshake. The HAVING threshold of three is for the demo; production uses hundreds within a TUMBLE window of 10 seconds so the count resets and a baseline join makes it adaptive.

Bandwidth per interface and volume anomalies per path use the same aggregation shape, and a port scan is one source hitting many ports on one target.

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;

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;

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;

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');
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

The view updated on the insert with no re-query: 198.51.100.10 probed SSH, Telnet, HTTP, HTTPS, and RDP on one host, all SYN-only, a textbook horizontal scan. The 320 KB max_single_transfer on eth0 in bandwidth_utilization is the kind of outlier worth a look, large query result, file transfer, or exfiltration, and layering one-minute, five-minute, and hourly TUMBLE views over the same source turns the static thresholds into baselines.

Radio Access Network KPIs: Cells, Sites, and Degradation Alerts

A degraded cell site affects thousands of subscribers at once, and 15-minute performance management files are too slow to act on. Per-cell counter snapshots from the base station or an O-RAN RAN Intelligent Controller arrive every minute; one-minute windows give RSRP, RSRQ, PRB utilization, throughput, round-trip time, and derived handover and RRC success rates; a five-minute site rollup gives the NOC map.

CREATE SOURCE cell_kpi_events (
    cell_id             VARCHAR,
    enb_id              VARCHAR,
    site_id             VARCHAR,
    technology          VARCHAR,    -- LTE, NR
    frequency_band      VARCHAR,
    rsrp_dbm            DOUBLE PRECISION,
    rsrq_db             DOUBLE PRECISION,
    prb_util_dl_pct     DOUBLE PRECISION,
    prb_util_ul_pct     DOUBLE PRECISION,
    throughput_dl_mbps  DOUBLE PRECISION,
    throughput_ul_mbps  DOUBLE PRECISION,
    rtt_ms              DOUBLE PRECISION,
    active_ue_count     INT,
    handover_attempts   BIGINT,
    handover_successes  BIGINT,
    rrc_attempts        BIGINT,
    rrc_successes       BIGINT,
    bearer_attempts     BIGINT,
    bearer_failures     BIGINT,
    event_time          TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'telecom.cell.kpi', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW cell_kpi_1min AS
SELECT cell_id, enb_id, site_id, technology, frequency_band, window_start, window_end,
       AVG(rsrp_dbm) AS avg_rsrp, AVG(rsrq_db) AS avg_rsrq,
       AVG(prb_util_dl_pct) AS avg_prb_dl, AVG(prb_util_ul_pct) AS avg_prb_ul,
       AVG(throughput_dl_mbps) AS avg_throughput_dl, AVG(throughput_ul_mbps) AS avg_throughput_ul, AVG(rtt_ms) AS avg_rtt_ms,
       AVG(active_ue_count) AS avg_ue_count,
       100.0 * SUM(handover_successes) / NULLIF(SUM(handover_attempts), 0) AS ho_success_rate_pct,
       100.0 * SUM(rrc_successes) / NULLIF(SUM(rrc_attempts), 0)           AS rrc_success_rate_pct
FROM TUMBLE(cell_kpi_events, event_time, INTERVAL '1 MINUTE')
GROUP BY cell_id, enb_id, site_id, technology, frequency_band, window_start, window_end;

CREATE MATERIALIZED VIEW site_kpi_5min AS
SELECT site_id, window_start, window_end, COUNT(DISTINCT cell_id) AS active_cells,
       AVG(avg_rsrp) AS site_avg_rsrp, AVG(avg_prb_dl) AS site_avg_prb_dl, SUM(avg_throughput_dl) AS site_total_throughput_dl,
       AVG(avg_rtt_ms) AS site_avg_rtt, AVG(ho_success_rate_pct) AS site_ho_success_rate, AVG(rrc_success_rate_pct) AS site_rrc_success_rate
FROM TUMBLE(cell_kpi_1min, window_start, INTERVAL '5 MINUTES')
GROUP BY site_id, window_start, window_end;

CREATE MATERIALIZED VIEW kpi_alerts AS
SELECT cell_id, site_id, technology, window_end AS alert_time, avg_rsrp, avg_rsrq, avg_prb_dl, ho_success_rate_pct, rrc_success_rate_pct, avg_rtt_ms,
       CASE WHEN avg_rsrp < -110 THEN 'POOR_COVERAGE' WHEN avg_prb_dl > 90 THEN 'CELL_CONGESTION'
            WHEN ho_success_rate_pct < 90 THEN 'HO_FAILURE' WHEN rrc_success_rate_pct < 95 THEN 'RRC_FAILURE'
            WHEN avg_rtt_ms > 100 THEN 'HIGH_LATENCY' ELSE 'KPI_DEGRADATION' END AS alert_type,
       CASE WHEN avg_rsrp < -115 OR avg_prb_dl > 95 OR ho_success_rate_pct < 80 THEN 'CRITICAL'
            WHEN avg_rsrp < -110 OR avg_prb_dl > 85 OR ho_success_rate_pct < 90 THEN 'WARNING' ELSE 'INFO' END AS severity
FROM cell_kpi_1min
WHERE avg_rsrp < -110 OR avg_prb_dl > 85 OR ho_success_rate_pct < 90 OR rrc_success_rate_pct < 95 OR avg_rtt_ms > 100;

Engineers see per-cell metrics refresh every minute, correlate LTE and NR layers on the same site, and trigger load balancing or parameter changes before the SLA is breached rather than after the PM file lands.

Capacity Planning: Congested Minutes, Busy Hour, and Backhaul

Reactive capacity management is expensive: a cell at 95 percent PRB utilization during the peak hour degrades every attached subscriber and generates complaints, churn, and helpdesk calls. The same counter stream, aggregated hourly with a count of congested minutes and a bearer failure rate, plus a daily busy-hour view and backhaul link utilization per site, gives planning a live signal days or weeks ahead of the weekly report.

CREATE SOURCE backhaul_events (
    link_id        VARCHAR,
    site_id        VARCHAR,
    capacity_mbps  DOUBLE PRECISION,
    utilized_mbps  DOUBLE PRECISION,
    event_time     TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'telecom.backhaul.utilization', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW cell_capacity_1h AS
SELECT cell_id, site_id, technology, frequency_band, window_start, window_end,
       AVG(prb_util_dl_pct) AS avg_prb_dl, MAX(prb_util_dl_pct) AS peak_prb_dl, AVG(prb_util_ul_pct) AS avg_prb_ul, MAX(prb_util_ul_pct) AS peak_prb_ul,
       AVG(throughput_dl_mbps) AS avg_throughput_dl, MAX(throughput_dl_mbps) AS peak_throughput_dl,
       AVG(active_ue_count) AS avg_ue_count, MAX(active_ue_count) AS peak_ue_count,
       SUM(bearer_attempts) AS total_bearer_attempts, SUM(bearer_failures) AS total_bearer_failures,
       100.0 * SUM(bearer_failures) / NULLIF(SUM(bearer_attempts), 0) AS bearer_failure_rate_pct,
       COUNT(*) FILTER (WHERE prb_util_dl_pct > 85) AS congested_minutes
FROM TUMBLE(cell_kpi_events, event_time, INTERVAL '1 HOUR')
GROUP BY cell_id, site_id, technology, frequency_band, window_start, window_end;

CREATE MATERIALIZED VIEW cell_busy_hour AS
SELECT cell_id, site_id, window_start AS day, MAX(peak_prb_dl) AS daily_peak_prb_dl, SUM(congested_minutes) AS daily_congested_minutes, AVG(avg_ue_count) AS daily_avg_ue
FROM TUMBLE(cell_capacity_1h, window_start, INTERVAL '1 DAY')
GROUP BY cell_id, site_id, window_start;

CREATE MATERIALIZED VIEW backhaul_util_1h AS
SELECT site_id, window_start, window_end, SUM(capacity_mbps) AS total_capacity_mbps, SUM(utilized_mbps) AS total_utilized_mbps,
       100.0 * SUM(utilized_mbps) / NULLIF(SUM(capacity_mbps), 0) AS util_pct, MAX(100.0 * utilized_mbps / NULLIF(capacity_mbps, 0)) AS max_link_util_pct
FROM TUMBLE(backhaul_events, event_time, INTERVAL '1 HOUR')
GROUP BY site_id, window_start, window_end;

CREATE MATERIALIZED VIEW capacity_alerts AS
SELECT c.cell_id, c.site_id, c.technology, c.window_end AS alert_time, c.avg_prb_dl, c.peak_prb_dl, c.congested_minutes, c.bearer_failure_rate_pct, b.util_pct AS backhaul_util_pct,
       CASE WHEN c.peak_prb_dl > 95 AND c.congested_minutes > 30 THEN 'SUSTAINED_CONGESTION'
            WHEN c.bearer_failure_rate_pct > 5 THEN 'BEARER_FAILURE'
            WHEN b.util_pct > 90 THEN 'BACKHAUL_SATURATION' ELSE 'CAPACITY_WARNING' END AS alert_type,
       CASE WHEN c.peak_prb_dl > 95 OR b.util_pct > 90 THEN 'CRITICAL' WHEN c.peak_prb_dl > 85 OR b.util_pct > 80 THEN 'WARNING' ELSE 'INFO' END AS severity
FROM cell_capacity_1h c
LEFT JOIN backhaul_util_1h b ON c.site_id = b.site_id AND c.window_start = b.window_start
WHERE c.peak_prb_dl > 85 OR c.bearer_failure_rate_pct > 3 OR b.util_pct > 80;

Thirty congested minutes in an hour with a peak above 95 percent is sustained congestion, not a spike, and the alert opens the upgrade ticket before the manual review cycle. A cell whose radio is fine while its site backhaul is above 90 percent points at the transport link, not the cell. Week-over-week growth is cell_busy_hour joined to itself seven days apart.

5G Network Slicing: Per-Slice SLA Compliance

Slices are commercial products with contractual SLAs. An URLLC slice for factory automation promises sub-5 millisecond latency; an eMBB slice for an enterprise campus promises a minimum throughput. Per-QoS-flow telemetry from the UPF, keyed by S-NSSAI, aggregates per slice each minute and is scored against the SLA table, with a five-minute trend for violation minutes.

CREATE SOURCE slice_qos_events (
    slice_id         VARCHAR,    -- S-NSSAI as SST:SD, e.g. 1:000001
    slice_type       VARCHAR,    -- eMBB, URLLC, mMTC
    qfi              INT,
    ue_imsi          VARCHAR,
    cell_id          VARCHAR,
    throughput_mbps  DOUBLE PRECISION,
    latency_ms       DOUBLE PRECISION,
    jitter_ms        DOUBLE PRECISION,
    packet_loss_pct  DOUBLE PRECISION,
    prb_allocated    INT,
    prb_used         INT,
    event_time       TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'telecom.5g.slice.qos', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE TABLE slice_sla (
    slice_id             VARCHAR PRIMARY KEY,
    slice_type           VARCHAR,
    min_throughput_mbps  DOUBLE PRECISION,
    max_latency_ms       DOUBLE PRECISION,
    max_jitter_ms        DOUBLE PRECISION,
    max_packet_loss_pct  DOUBLE PRECISION,
    tenant_id            VARCHAR
);

CREATE MATERIALIZED VIEW slice_kpi_1min AS
SELECT slice_id, slice_type, window_start, window_end, COUNT(DISTINCT ue_imsi) AS active_ues,
       AVG(throughput_mbps) AS avg_throughput, MIN(throughput_mbps) AS min_throughput,
       AVG(latency_ms) AS avg_latency, MAX(latency_ms) AS max_latency, AVG(jitter_ms) AS avg_jitter, AVG(packet_loss_pct) AS avg_packet_loss,
       SUM(prb_allocated) AS total_prb_allocated, SUM(prb_used) AS total_prb_used,
       100.0 * SUM(prb_used) / NULLIF(SUM(prb_allocated), 0) AS prb_efficiency_pct
FROM TUMBLE(slice_qos_events, event_time, INTERVAL '1 MINUTE')
GROUP BY slice_id, slice_type, window_start, window_end;

CREATE MATERIALIZED VIEW slice_sla_compliance AS
SELECT k.slice_id, k.slice_type, k.window_start, k.window_end, k.active_ues, k.avg_throughput, k.avg_latency, k.max_latency, k.avg_jitter, k.avg_packet_loss, k.prb_efficiency_pct,
       s.min_throughput_mbps AS sla_min_throughput, s.max_latency_ms AS sla_max_latency, s.max_packet_loss_pct AS sla_max_packet_loss, s.tenant_id,
       k.avg_throughput >= s.min_throughput_mbps  AS throughput_compliant,
       k.avg_latency <= s.max_latency_ms           AS latency_compliant,
       k.avg_packet_loss <= s.max_packet_loss_pct  AS packet_loss_compliant,
       CASE WHEN k.avg_throughput >= s.min_throughput_mbps THEN 0.4 ELSE 0.0 END
     + CASE WHEN k.avg_latency <= s.max_latency_ms          THEN 0.4 ELSE 0.0 END
     + CASE WHEN k.avg_packet_loss <= s.max_packet_loss_pct THEN 0.2 ELSE 0.0 END AS sla_compliance_score
FROM slice_kpi_1min k
JOIN slice_sla s ON k.slice_id = s.slice_id;

CREATE MATERIALIZED VIEW slice_trends_5min AS
SELECT slice_id, window_start, window_end, AVG(avg_throughput) AS trend_throughput, AVG(avg_latency) AS trend_latency, AVG(sla_compliance_score) AS avg_sla_score,
       COUNT(*) FILTER (WHERE NOT latency_compliant) AS latency_violation_minutes, COUNT(*) FILTER (WHERE NOT throughput_compliant) AS throughput_violation_minutes
FROM TUMBLE(slice_sla_compliance, window_start, INTERVAL '5 MINUTES')
GROUP BY slice_id, window_start, window_end;

CREATE MATERIALIZED VIEW slice_sla_alerts AS
SELECT slice_id, slice_type, tenant_id, window_end AS alert_time, avg_throughput, avg_latency, max_latency, avg_packet_loss, sla_compliance_score,
       CASE WHEN NOT latency_compliant AND slice_type = 'URLLC' THEN 'URLLC_LATENCY_SLA_BREACH'
            WHEN NOT throughput_compliant AND slice_type = 'eMBB' THEN 'EMBB_THROUGHPUT_SLA_BREACH'
            WHEN NOT packet_loss_compliant THEN 'PACKET_LOSS_SLA_BREACH' ELSE 'SLA_DEGRADATION' END AS alert_type,
       CASE WHEN sla_compliance_score < 0.4 THEN 'CRITICAL' WHEN sla_compliance_score < 0.8 THEN 'WARNING' ELSE 'INFO' END AS severity
FROM slice_sla_compliance
WHERE sla_compliance_score < 0.8;

For an URLLC slice the SLA is usually on the tail, so max_latency or a percentile is the right compliance check rather than the average. PRB efficiency below the allocation tells the slice manager where resources can be reclaimed for another tenant, and the compliance view filtered by tenant_id is the live portal the tenant sees.

Subscriber Experience: Per-IMSI Quality of Experience and Retention Triggers

Churn is preceded by degraded experience, and Net Promoter Score surveys report it after the fact. UE measurement reports and data session events keyed by IMSI give an hourly signal score, throughput score, and reliability score, weighted into one quality of experience score. A subscriber with three dropped sessions and RSRP below -110 dBm in the past hour is a retention trigger now, not a churn statistic next quarter.

CREATE SOURCE ue_measurement_reports (
    imsi VARCHAR, imei VARCHAR, cell_id VARCHAR, rsrp_dbm DOUBLE PRECISION, rsrq_db DOUBLE PRECISION, sinr_db DOUBLE PRECISION, event_time TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'telecom.ue.measurements', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE data_session_events (
    imsi VARCHAR, session_id VARCHAR, throughput_dl_mbps DOUBLE PRECISION, throughput_ul_mbps DOUBLE PRECISION,
    latency_ms DOUBLE PRECISION, jitter_ms DOUBLE PRECISION, packet_loss_pct DOUBLE PRECISION,
    session_state VARCHAR,      -- 'ACTIVE', 'DROPPED', 'COMPLETED'
    event_time TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'telecom.data.sessions', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW subscriber_signal_1h AS
SELECT imsi, window_start, window_end, AVG(rsrp_dbm) AS avg_rsrp, AVG(rsrq_db) AS avg_rsrq, AVG(sinr_db) AS avg_sinr, COUNT(*) AS measurement_count,
       COUNT(DISTINCT cell_id) AS cells_seen
FROM TUMBLE(ue_measurement_reports, event_time, INTERVAL '1 HOUR')
GROUP BY imsi, window_start, window_end;

CREATE MATERIALIZED VIEW subscriber_session_1h AS
SELECT imsi, window_start, window_end, AVG(throughput_dl_mbps) AS avg_throughput_dl, AVG(latency_ms) AS avg_latency, AVG(jitter_ms) AS avg_jitter,
       AVG(packet_loss_pct) AS avg_packet_loss, COUNT(*) FILTER (WHERE session_state = 'DROPPED') AS dropped_sessions, COUNT(*) AS total_sessions
FROM TUMBLE(data_session_events, event_time, INTERVAL '1 HOUR')
GROUP BY imsi, window_start, window_end;

CREATE MATERIALIZED VIEW subscriber_qoe_1h AS
SELECT sig.imsi, sig.window_start, sig.window_end, sig.avg_rsrp, sig.avg_rsrq, ses.avg_throughput_dl, ses.avg_latency, ses.avg_packet_loss, ses.dropped_sessions, ses.total_sessions,
       signal_score, throughput_score, reliability_score,
       signal_score * 0.3 + throughput_score * 0.4 + reliability_score * 0.3 AS qoe_score
FROM subscriber_signal_1h sig
JOIN subscriber_session_1h ses ON sig.imsi = ses.imsi AND sig.window_start = ses.window_start
CROSS JOIN LATERAL (
    SELECT CASE WHEN sig.avg_rsrp >= -85 THEN 1.0 WHEN sig.avg_rsrp >= -100 THEN 0.75 WHEN sig.avg_rsrp >= -110 THEN 0.5 WHEN sig.avg_rsrp >= -115 THEN 0.25 ELSE 0.0 END AS signal_score,
           LEAST(1.0, GREATEST(0.0, ses.avg_throughput_dl / 50.0)) AS throughput_score,
           CASE WHEN ses.total_sessions > 0 THEN 1.0 - ses.dropped_sessions::DOUBLE PRECISION / ses.total_sessions ELSE 1.0 END AS reliability_score
) scores;

CREATE MATERIALIZED VIEW poor_qoe_subscribers AS
SELECT imsi, window_end AS assessed_at, avg_rsrp, avg_throughput_dl, dropped_sessions, total_sessions, qoe_score,
       CASE WHEN qoe_score < 0.3 THEN 'CRITICAL' WHEN qoe_score < 0.5 THEN 'POOR' ELSE 'FAIR' END AS qoe_band
FROM subscriber_qoe_1h
WHERE qoe_score < 0.5;

Because the score is per IMSI and per hour, joining poor_qoe_subscribers to kpi_alerts on the serving cell separates a network problem affecting everyone on the cell, which is a NOC ticket, from a device or location problem affecting one subscriber, which is a retention offer such as a network booster or a bill credit. CRM and retention platforms query the view directly over PostgreSQL with no separate API.

Routing Alerts and Serving the NOC

CREATE SINK ddos_alerts_sink FROM ddos_alerts
WITH (connector = 'kafka', properties.bootstrap.server = 'kafka:9092', topic = 'network.ddos.alerts', primary_key = 'dst_ip,dst_port,interface_id')
FORMAT UPSERT ENCODE JSON;

CREATE SINK port_scan_sink FROM port_scan_detection
WITH (connector = 'kafka', properties.bootstrap.server = 'kafka:9092', topic = 'network.portscan.alerts', primary_key = 'src_ip,dst_ip')
FORMAT UPSERT ENCODE JSON;

CREATE SINK kpi_alerts_sink FROM kpi_alerts
WITH (connector = 'kafka', topic = 'telecom.noc.alerts', properties.bootstrap.server = 'kafka:9092') FORMAT PLAIN ENCODE JSON;

CREATE SINK capacity_alerts_sink FROM capacity_alerts
WITH (connector = 'kafka', topic = 'telecom.capacity.alerts', properties.bootstrap.server = 'kafka:9092') FORMAT PLAIN ENCODE JSON;

CREATE SINK slice_sla_alerts_sink FROM slice_sla_alerts
WITH (connector = 'kafka', topic = 'telecom.5g.slice.alerts', properties.bootstrap.server = 'kafka:9092') FORMAT PLAIN ENCODE JSON;

CREATE SINK poor_qoe_sink FROM poor_qoe_subscribers
WITH (connector = 'kafka', topic = 'telecom.retention.triggers', properties.bootstrap.server = 'kafka:9092') FORMAT PLAIN ENCODE JSON;

CREATE SINK site_kpi_sink FROM site_kpi_5min
WITH (connector = 'jdbc', jdbc.url = 'jdbc:postgresql://noc-db:5432/telemetry', table.name = 'site_kpi_5min', type = 'upsert', primary_key = 'site_id,window_start');

Network devices export telemetry to Kafka, RisingWave maintains one view per monitoring dimension, alert views sink to Kafka topics consumed by PagerDuty, Slack, firewall automation, and the NOC ticketing system, and summary views serve Grafana over PostgreSQL. Streaming SQL complements a SIEM rather than replacing it: it handles the detect-fast half, and the SIEM keeps the long-horizon forensics.

Polling and PM Files vs Streaming SQL

ConcernTraditional toolingStreaming SQL (RisingWave)
SYN flood, port scanNetFlow export plus SIEM polling, minutesDistinct-source and distinct-port counts updated per record
Cell degradation15-minute PM files processed in batchOne-minute KPIs, severity-classified alerts per cell
CapacityWeekly aggregated reportsCongested minutes per hour, busy hour per day, backhaul per site
Slice SLA15-minute slice KPI intervalsPer-minute compliance score against the SLA table, tenant-visible
Subscriber experienceNPS surveys and churn reportsHourly per-IMSI QoE score, retention trigger inside the hour

Frequently Asked Questions

What network data sources work with RisingWave? Anything structured: NetFlow, sFlow, and IPFIX records in Kafka, syslog from firewalls and routers, Zeek or Suricata output, cloud VPC flow logs, and vendor performance counters from base stations, O-RAN controllers, UPFs, and probes. Sources also include Pulsar, Kinesis, and CDC from PostgreSQL and MySQL for reference data like the SLA table.

How are the static thresholds made adaptive? Maintain a long-window view, hourly or 24-hour, per destination, cell, or slice, and join the short-window view against it to compute a ratio. Alert when the current window exceeds a multiple of the baseline rather than a fixed number.

Can the packet tutorial views be windowed? Yes. Replace FROM network_packets with FROM TUMBLE(network_packets, event_time, INTERVAL '10 seconds') and add window_start, window_end to the GROUP BY. Unwindowed views accumulate forever, which is fine for a demo and wrong for production.

How does the subscriber QoE view handle subscribers with signal reports but no sessions? The inner join drops them. Switch to a left join from the signal view and default the throughput and reliability scores to 1.0 or to NULL depending on whether an idle subscriber should count as satisfied or as unknown.

Does RisingWave replace the SIEM or the performance management system? No. It provides the real-time detection and aggregation layer both of them lack. Raw records stay in Kafka and the long-term stores; RisingWave sinks alerts and summaries into them and serves the live views.

The engine for real-time streaming data.
Analyze, search, and decide.
GitHubXLinkedInSlackYouTube
Sign up for our to stay updated.