From Kafka Events to Real-Time Alerts: A Hands-On Guide with ksqlDB

Real-time data is everywhere: logins, payments, sensors, clicks. The value of this data often fades within minutes — or even seconds — if you don’t act on it.

You could write custom Kafka consumers and build an event-processing microservice, but that involves coding, deployment, and testing before you even get to the business logic.

This is where streaming SQL shines. With just SQL queries, you can continuously process events as they arrive, spot suspicious patterns, and trigger actions in real time.

Among the available tools, including Apache Flink SQL, Spark Structured Streaming, RisingWave, and ksqlDB, each has its strengths. Flink is now considered the most advanced option, and Confluent has shifted much of its focus there. But ksqlDB remains one of the easiest ways to get started, especially if you already use Kafka. It’s lightweight, simple to spin up, and ideal for proof-of-concepts or smaller real-time use cases that don’t require writing Java or Python.

In this article, I’ll show you how to use ksqlDB to detect brute-force login attempts in real time. We’ll build the environment with Docker, stream login events into Kafka, define a detection rule in SQL, and generate alerts that downstream systems can consume.

Detect Brute-force Login Attempts in Real Time

Step 1: Environment Setup

We’ll use Docker Compose to run ZooKeeper, Kafka broker, Schema Registry, ksqlDB server, and client.

services:
  zookeeper:
    image: confluentinc/cp-zookeeper:7.4.0
    ports:
      - "2181:2181"
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181

  kafka:
    image: confluentinc/cp-kafka:7.4.0
    ports:
      - "9092:9092"     # internal (Docker network)
      - "29092:29092"   # external (host -> container)
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181

      # Two listeners: one for containers, one for your laptop
      KAFKA_LISTENERS: PLAINTEXT://0.0.0.0:9092,PLAINTEXT_HOST://0.0.0.0:29092
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092,PLAINTEXT_HOST://localhost:29092
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_INTER_BROKER_LISTENER_NAME: PLAINTEXT

      # Single-broker friendly settings
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_TRANSACTION_STATE_LOG_REPLICATION_FACTOR: 1
      KAFKA_TRANSACTION_STATE_LOG_MIN_ISR: 1
      KAFKA_GROUP_INITIAL_REBALANCE_DELAY_MS: 0
    depends_on:
      - zookeeper

  schema-registry:
    image: confluentinc/cp-schema-registry:7.4.0
    ports:
      - "8081:8081"
    environment:
      SCHEMA_REGISTRY_HOST_NAME: schema-registry
      SCHEMA_REGISTRY_LISTENERS: http://0.0.0.0:8081
      SCHEMA_REGISTRY_KAFKASTORE_BOOTSTRAP_SERVERS: PLAINTEXT://kafka:9092
    depends_on:
      - kafka

  ksqldb-server:
    image: confluentinc/cp-ksqldb-server:7.4.0
    ports:
      - "8088:8088"
    environment:
      KSQL_LISTENERS: http://0.0.0.0:8088
      KSQL_BOOTSTRAP_SERVERS: kafka:9092
      KSQL_KSQL_SCHEMA_REGISTRY_URL: http://schema-registry:8081
      KSQL_KSQL_SERVICE_ID: "ksql_local_"
      KSQL_KSQL_LOGGING_PROCESSING_STREAM_AUTO_CREATE: "true"
      KSQL_KSQL_LOGGING_PROCESSING_TOPIC_AUTO_CREATE: "true"
    depends_on:
      - kafka
      - schema-registry

  ksqldb-cli:
    image: confluentinc/cp-ksqldb-cli:7.4.0
    container_name: ksqldb-cli
    depends_on:
      - ksqldb-server
    entrypoint: ["/bin/sh","-c","sleep infinity"]  # keep the container running
    tty: true
    volumes:
      - ./ksql:/scripts   # optional: put .sql files here

Step 2: Create Kafka Topic

Once the stack is started and services are all up and running, we create a Kafka topic for the login attempts

kafka-topics --bootstrap-server localhost:29092 --create --topic login_attempts --partitions 1 --replication-factor 1Step 3: Example Event Schema

The events will have the following schema:

{
  "user_id": "u123",
  "ip": "203.0.113.42",
  "success": false,
  "event_time": "2025-08-20T10:05:30.000Z",
  "ua": "Mozilla/5.0"
}

In this schema, event_time is the source-of-truth timestamp. And the data will be partitioned by user_id, ensuring accurate per-user aggregations.

Step 3: Define the ksqlDB Streams

Open ksqlDB CLI:

docker exec -it ksqldb-cli ksql http://ksqldb-server:8088

Create the source stream over your existing topic using AVRO:

SET 'auto.offset.reset'='earliest';

CREATE STREAM login_attempts_raw (
  user_id STRING KEY,
  ip STRING,
  success BOOLEAN,
  event_time TIMESTAMP,
  ua STRING
) WITH (
  KAFKA_TOPIC='login_attempts',
  VALUE_FORMAT='AVRO',
  PARTITIONS=1,
  TIMESTAMP='event_time'
);

By setting TIMESTAMP=event_time, KsqlDB uses that field as the Kafka record timestamp for windowing; otherwise, it uses processing time.

Step 4: Detection Rule (Windowed Aggregation)

We flag a user if they attempt three failed logins within five minutes. To do that, we need to check “any” 5-minute span (more sensitive than fixed 5-minute blocks) and emit “alerts” only when the count crosses 5.

CREATE TABLE failed_login_alerts
  WITH (KAFKA_TOPIC='failed_login_alerts', VALUE_FORMAT='AVRO') AS
SELECT
  user_id,
  COUNT(*)      AS fail_count,
  WINDOWSTART   AS window_start,
  WINDOWEND     AS window_end
FROM login_attempts_raw
WINDOW HOPPING (SIZE 5 MINUTES, ADVANCE BY 1 MINUTE, GRACE PERIOD 1 MINUTE)
WHERE success = false
GROUP BY user_id
HAVING COUNT(*) = 3
EMIT CHANGES;

This query creates a table indexed by user_id and window that updates as events arrive.

The ADVANCED BY 1 MINUTE creates a new hopping window that starts every minute, and GRACE PERIOD lets slightly late events still count.

Step 5: Watch Alerts Live

Run the following command and produce a burst of failures for a user.

SELECT * FROM failed_login_alerts EMIT CHANGES;

As soon as the entry records for a user match the query condition, you will see a record of the user.

Once you have the stream, consumers can read the failed_login_alerts table as “alert events” and respond to them.

Final Thoughts

Streaming SQL isn’t here to replace full-blown stream processing frameworks, but it fills an important gap: making real-time event processing accessible without heavy coding.

KsqlDB may not be the most advanced streaming SQL engine, but it’s still one of the simplest ways to turn Kafka events into real-time insights.

The brute-force detection example here is basic, yet it demonstrates how quickly you can transform raw events into actionable alerts with just SQL. For anyone starting with real-time processing, ksqlDB remains a practical entry point before moving on to heavier frameworks.