Detect lateral movement in real time by ingesting authentication logs from Kafka into RisingWave, then running a SQL materialized view that counts distinct destination hosts per source account inside a session window. When the count crosses a threshold within minutes, RisingWave triggers an alert before the attacker reaches sensitive assets.
What is lateral movement and how does it appear in logs?
After compromising a single host, attackers do not stop there. They pivot across the internal network by authenticating to one host after another, harvesting credentials along the way. This technique is called lateral movement. Common methods include Pass-the-Hash, Pass-the-Ticket, and reuse of stolen plaintext credentials.
Every authentication attempt generates a log event. In Windows environments these appear as Event ID 4624 (successful logon) or 4648 (explicit credential logon). In Linux environments, PAM and SSH produce structured syslog entries. Each event shares a common schema: a source host (where the authentication originated), a destination host (the target machine), a user account, an authentication method, and a precise timestamp.
A single attacker session leaves a visible trail: the same account authenticates to host-a, then host-b, then host-c within a few minutes. No individual event looks suspicious in isolation. The pattern only surfaces when you correlate events across time.
Why streaming SQL detects lateral movement better than batch analytics?
Batch analytics systems process data on a schedule, typically every 15 minutes, every hour, or once a day. An attacker moving laterally across five machines in eight minutes completes the reconnaissance and possibly the exfiltration before the next batch job even starts. By the time the SIEM generates an alert, the damage is done.
Streaming SQL evaluates continuously. Every new authentication event immediately updates the running count of destination hosts for each source account. There is no polling interval, no scheduled job, and no delay between the event arriving and the detection query re-evaluating.
RisingWave maintains persistent, incrementally updated materialized views over live Kafka streams. When a new event arrives, only the affected session window is recalculated, not the entire dataset. This makes it possible to sustain sub-second latency even at millions of authentication events per hour, without the operational overhead of a custom stateful application.
How to model lateral movement as a SQL detection rule?
The core detection logic is straightforward: count the number of distinct destination hosts that a single account touches within a short time window. If that count exceeds a threshold (typically four or five unique hosts), flag the account as a lateral movement suspect.
Session windows are the right window type here. Unlike tumbling windows that cut time into fixed buckets, a session window extends as long as activity continues and closes only after a configurable gap of inactivity. This matches how attackers behave: they move in bursts with occasional pauses.
The SQL pattern looks like this:
SELECT
account,
window_start,
window_end,
COUNT(DISTINCT dest_host) AS unique_hosts,
array_agg(DISTINCT dest_host) AS host_list
FROM SESSION(auth_events, timestamp, INTERVAL '5 minutes')
GROUP BY account, window_start, window_end
HAVING COUNT(DISTINCT dest_host) >= 5;
Any account that reaches five or more distinct destination hosts within a single session window is surfaced as a candidate for investigation. The array_agg column gives analysts the exact sequence of hosts to examine immediately.
Step-by-step implementation with RisingWave
Step 1: Create the Kafka source
Authentication events arrive as JSON in a Kafka topic named auth_logs. Define a source that maps the JSON fields to typed columns:
CREATE SOURCE auth_events (
src_host VARCHAR,
dest_host VARCHAR,
account VARCHAR,
auth_method VARCHAR,
status VARCHAR,
ts TIMESTAMPTZ
)
WITH (
connector = 'kafka',
topic = 'auth_logs',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON (
timestamptz_handling = 'utc'
);
This source ingests only successful authentications from the stream as they land. You can add a WHERE status = 'success' filter in the downstream materialized view.
Step 2: Create the lateral movement detection view
CREATE MATERIALIZED VIEW lateral_movement_alerts AS
WITH session_agg AS (
SELECT
account,
src_host,
window_start,
window_end,
COUNT(DISTINCT dest_host) AS unique_dest_hosts,
array_agg(DISTINCT dest_host ORDER BY dest_host) AS dest_host_list
FROM SESSION(
(SELECT * FROM auth_events WHERE status = 'success'),
ts,
INTERVAL '5 minutes'
)
GROUP BY account, src_host, window_start, window_end
)
SELECT
sa.account,
sa.src_host,
sa.window_start,
sa.window_end,
sa.unique_dest_hosts,
sa.dest_host_list,
COALESCE(a.sensitivity, 'low') AS max_asset_sensitivity,
NOW() AS detected_at
FROM session_agg sa
LEFT JOIN asset_sensitivity a
ON a.hostname = ANY(sa.dest_host_list)
WHERE sa.unique_dest_hosts >= 5
ORDER BY sa.unique_dest_hosts DESC;
The asset_sensitivity table is a static dimension table that maps hostnames to sensitivity tiers (critical, high, medium, low). The join enriches each alert with the highest-value asset that was accessed during the session.
Once this materialized view is created, RisingWave continuously maintains it. Querying it at any moment returns the current set of suspicious sessions with no added latency.
Step 3: Sink alerts downstream
Connect an alert sink to forward rows from lateral_movement_alerts to PagerDuty, Slack, or a SIEM:
CREATE SINK alert_sink FROM lateral_movement_alerts
WITH (
connector = 'kafka',
topic = 'security_alerts',
properties.bootstrap.server = 'kafka:9092'
)
FORMAT UPSERT ENCODE JSON;
How to reduce false positives in lateral movement detection?
A raw threshold of five destination hosts will catch attackers, but it will also catch legitimate administrators running deployment scripts, monitoring agents polling dozens of servers, and batch jobs authenticating to distributed storage nodes.
Tune the window size. A 5-minute session gap is tight for attackers but may be too short for scheduled tasks. Start at 5 minutes and adjust based on your observed false positive rate. For environments with slower attacker tooling, 10 minutes still provides early detection while reducing noise significantly.
Exclude service accounts. Automation accounts, monitoring agents, and CI/CD pipelines routinely authenticate to many hosts. Maintain a service_accounts table and filter them out in the materialized view with a simple NOT EXISTS or LEFT JOIN ... WHERE service_accounts.account IS NULL clause.
Weight by asset sensitivity. Reaching five low-value development machines carries a different risk score than reaching one critical production database server. The asset_sensitivity join in the materialized view above allows you to set a lower threshold for sessions that touch critical assets, for example flagging any session that accesses two or more critical hosts regardless of total host count.
Add a minimum event count. Require at least three authentication events in the session, not just two hops to five different hosts through a single failed attempt. This suppresses alerts caused by misconfigured clients that spray connection attempts.
How does this compare to Flink-based lateral movement detection?
Apache Flink is the most common alternative for stateful stream processing. Both RisingWave and Flink can implement lateral movement detection, but the developer experience differs significantly.
SQL vs DataStream API. In Flink, implementing a session-windowed COUNT DISTINCT with a dimension table join requires the DataStream API or the lower-level Table API. It involves writing a ProcessWindowFunction, managing a ValueState for distinct host tracking, and handling late-arriving events manually. RisingWave expresses the same logic in twelve lines of standard SQL.
Deployment complexity. Flink requires a dedicated JobManager, TaskManagers, a state backend (usually RocksDB or S3), and a checkpoint coordinator. RisingWave is a PostgreSQL-compatible database: connect with any Postgres client, run CREATE MATERIALIZED VIEW, and the streaming computation starts immediately. There is no separate cluster to operate for the detection logic itself.
Incremental correctness. RisingWave uses differential dataflow to incrementally maintain materialized views with exactly-once semantics. When an event arrives late (due to network delay), RisingWave retracts and corrects the affected window result automatically. Achieving the same correctness guarantee in Flink requires careful watermark configuration and explicit late-data side outputs.
For teams already running Flink for other pipelines, RisingWave can coexist: ingest from the same Kafka topics, and let analysts write detection rules in SQL without involving Flink developers.
Frequently asked questions
Can RisingWave handle the volume of authentication events in a large enterprise?
Yes. RisingWave scales horizontally. A large enterprise generating millions of authentication events per hour distributes the ingestion and computation across multiple nodes. The materialized view is maintained incrementally, meaning each new event triggers only a small update to the affected session window rather than a full re-scan of historical data.
What authentication log formats does RisingWave support?
RisingWave ingests any data format that arrives on Kafka, including JSON, Avro, and Protobuf. If your SIEM or log shipper (Filebeat, Fluentd, Splunk Universal Forwarder) already forwards auth events to Kafka, RisingWave connects directly. For Windows Event Log, tools like Winlogbeat can forward Event ID 4624/4648 records to Kafka with no custom development.
How do I avoid alerting on the same ongoing session repeatedly?
Use the window_start and account as a composite key for deduplication in your downstream alert system. RisingWave emits a retraction followed by an insertion whenever a session window updates, so alerting systems that support upsert semantics (like the Kafka sink with FORMAT UPSERT) will automatically consolidate updates into a single alert per session.
Is RisingWave suitable for regulated environments that require exactly-once event processing?
Yes. RisingWave provides exactly-once semantics end to end when reading from Kafka and writing to a transactional sink. Audit trails built on RisingWave materialized views are consistent: no event is counted twice and no event is silently dropped, even across node restarts or network partitions.
Lateral movement is one of the most reliably detectable phases of an attack, provided your analytics layer keeps up with the attacker's pace. RisingWave brings continuous, SQL-native detection to authentication log streams without requiring a distributed stream processing cluster or custom application code.
Start with the schema above, connect your Kafka auth log topic, and you can have a working lateral movement detection view running in under an hour.
Try RisingWave Cloud free or explore the RisingWave documentation to learn more about streaming SQL for security analytics.
