Build a SIEM-style security event platform. Ingest network logs, detect threats, and investigate incidents with fast time-range queries.

Note
 This tutorial assumes you have a running Ferrosa cluster. If you have not set one up yet, follow the Getting Started guide first. You should be able to connect with cqlsh before continuing.

Create the keyspace

Security event data is write-heavy and time-sensitive. You are going to be ingesting thousands of events per second and querying them by time range. A replication factor of 3 ensures no event is lost, even during node failures.

CREATE KEYSPACE IF NOT EXISTS security
  WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3};

USE security;

Design the schema

A security monitoring platform needs four core tables: a primary event log (partitioned by date for time-range queries), a secondary view by source IP (for investigating specific hosts), a threat intelligence lookup table, and an incident tracker. Let’s build them one at a time.

Events table

This is your primary log. Every firewall event, IDS alert, and authentication attempt lands here. Partitioning by event_date means each day’s events live in their own partition, which keeps time-range queries fast and gives you a natural data lifecycle — old partitions can be archived or dropped.

CREATE TABLE IF NOT EXISTS events (
    event_date   date,
    event_time   timestamp,
    event_id     uuid,
    source_ip    text,
    dest_ip      text,
    source_port  int,
    dest_port    int,
    protocol     text,
    event_type   text,
    severity     text,
    rule_id      text,
    description  text,
    raw_payload  blob,
    PRIMARY KEY ((event_date), event_time, event_id)
) WITH CLUSTERING ORDER BY (event_time DESC);
Note
 Why partition by date? Security analysts work in time windows — "show me everything from the last hour" or "what happened yesterday between 2am and 4am." Date partitioning makes these queries hit a single partition (or at most two, if the window spans midnight).

Events by source IP

When investigating a specific host, you need to pull all events from that IP quickly. This table uses a compound partition key of (source_ip, event_date) to keep per-IP, per-day data together:

    source_ip   text,
    event_date  date,
    event_time  timestamp,
    event_id    uuid,
    event_type  text,
    severity    text,
    dest_ip     text,
    dest_port   int,
    PRIMARY KEY ((source_ip, event_date), event_time, event_id)
) WITH CLUSTERING ORDER BY (event_time DESC);

This is denormalization in action — the same event data appears in both tables, optimized for different query patterns. You write to both tables when an event arrives.

Threat intelligence table

This stores known indicators of compromise (IOCs): malicious IP addresses, domain names, file hashes. Your detection engine checks incoming events against this table to flag known threats.

    indicator_type  text,
    indicator_value text,
    threat_name     text,
    severity        text,
    source          text,
    first_seen      timestamp,
    last_seen       timestamp,
    tags            set<text>,
    PRIMARY KEY (indicator_type, indicator_value)
);

Partitioning by indicator_type (like "ip", "domain", or "hash") groups similar indicators together. Lookups by type and value are O(1).

Incidents table

When events are correlated into a confirmed security incident, the details go here. Each incident tracks its lifecycle from creation through investigation to resolution.

    incident_id    uuid PRIMARY KEY,
    title          text,
    severity       text,
    status         text,
    created_at     timestamp,
    updated_at     timestamp,
    assigned_to    text,
    related_events list<uuid>,
    notes          text
);

The related_events column uses a list to store references to the event UUIDs that make up this incident.

Here is how the tables relate to each other:

Table Partition Key Clustering Key Serves

events

event_date

event_time DESC

Time-range event queries

events_by_source

(source_ip, event_date)

event_time DESC

Per-IP investigation

threat_intel

indicator_type

indicator_value

IOC lookups

incidents

incident_id

 — 

Incident lifecycle tracking

Two tables for the same events (with different partition keys) is a deliberate choice. In Ferrosa, you optimize for the read path by duplicating writes. The cost of writing twice is trivial compared to the cost of a slow investigation query during an active breach.

Your schema is ready — let’s fill it with security events.

Populate with sample data

Insert normal network traffic

First, some baseline activity that looks completely routine — web browsing, DNS queries, and internal service communication:

-- ── Normal Network Traffic ──

-- Normal web traffic
INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 08:01:00+0000', e1000000-0000-0000-0000-000000000001, '10.0.1.50', '93.184.216.34', 52431, 443, 'TCP', 'connection', 'info', 'NET-001', 'HTTPS connection to example.com');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 08:01:05+0000', e1000000-0000-0000-0000-000000000002, '10.0.1.50', '10.0.0.2', 49152, 53, 'UDP', 'dns_query', 'info', 'DNS-001', 'DNS lookup for internal.corp.local');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 08:05:00+0000', e1000000-0000-0000-0000-000000000003, '10.0.2.10', '10.0.3.20', 55000, 5432, 'TCP', 'connection', 'info', 'NET-001', 'Internal database connection from app server');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 08:10:00+0000', e1000000-0000-0000-0000-000000000004, '10.0.1.100', '10.0.0.1', 60123, 389, 'TCP', 'authentication', 'info', 'AUTH-001', 'Successful LDAP authentication for user jdoe');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 09:00:00+0000', e1000000-0000-0000-0000-000000000005, '10.0.1.75', '172.217.14.206', 51888, 443, 'TCP', 'connection', 'info', 'NET-001', 'HTTPS connection to google.com');

Insert SSH brute force attack

Now let’s simulate an attacker trying to brute-force SSH credentials. Notice the rapid succession of failed login attempts from the same source IP:

-- ── SSH Brute Force Attack ──

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 10:15:01+0000', e2000000-0000-0000-0000-000000000001, '203.0.113.42', '10.0.1.5', 44001, 22, 'TCP', 'auth_failure', 'medium', 'SSH-001', 'Failed SSH login attempt: user root');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 10:15:03+0000', e2000000-0000-0000-0000-000000000002, '203.0.113.42', '10.0.1.5', 44002, 22, 'TCP', 'auth_failure', 'medium', 'SSH-001', 'Failed SSH login attempt: user admin');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 10:15:05+0000', e2000000-0000-0000-0000-000000000003, '203.0.113.42', '10.0.1.5', 44003, 22, 'TCP', 'auth_failure', 'medium', 'SSH-001', 'Failed SSH login attempt: user ubuntu');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 10:15:07+0000', e2000000-0000-0000-0000-000000000004, '203.0.113.42', '10.0.1.5', 44004, 22, 'TCP', 'auth_failure', 'high', 'SSH-002', 'SSH brute force detected: 4 failures in 6 seconds from 203.0.113.42');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 10:15:08+0000', e2000000-0000-0000-0000-000000000005, '203.0.113.42', '10.0.1.5', 44005, 22, 'TCP', 'auth_failure', 'medium', 'SSH-001', 'Failed SSH login attempt: user deploy');

-- ── Port Scan Activity ──

Insert port scan activity

VALUES ('2026-03-17', '2026-03-17 11:30:00+0000', e3000000-0000-0000-0000-000000000001, '198.51.100.77', '10.0.1.5', 33000, 21, 'TCP', 'port_scan', 'medium', 'SCAN-001', 'SYN scan detected on port 21 (FTP)');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 11:30:01+0000', e3000000-0000-0000-0000-000000000002, '198.51.100.77', '10.0.1.5', 33001, 23, 'TCP', 'port_scan', 'medium', 'SCAN-001', 'SYN scan detected on port 23 (Telnet)');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 11:30:02+0000', e3000000-0000-0000-0000-000000000003, '198.51.100.77', '10.0.1.5', 33002, 80, 'TCP', 'port_scan', 'low', 'SCAN-001', 'SYN scan detected on port 80 (HTTP)');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 11:30:03+0000', e3000000-0000-0000-0000-000000000004, '198.51.100.77', '10.0.1.5', 33003, 3389, 'TCP', 'port_scan', 'high', 'SCAN-002', 'SYN scan on port 3389 (RDP) -- high-value target port');

-- ── Malware Beacon and Lateral Movement ──

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 13:00:00+0000', e4000000-0000-0000-0000-000000000001, '10.0.1.200', '45.33.32.156', 49200, 443, 'TCP', 'c2_beacon', 'critical', 'MAL-001', 'Outbound connection to known C2 server (Cobalt Strike)');

Insert malware beacon

This is the most dangerous pattern — an internal host communicating with a known command-and-control server at regular intervals:

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 13:05:00+0000', e4000000-0000-0000-0000-000000000002, '10.0.1.200', '45.33.32.156', 49201, 443, 'TCP', 'c2_beacon', 'critical', 'MAL-001', 'Repeated beacon to C2 server -- 5 minute interval detected');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 13:10:00+0000', e4000000-0000-0000-0000-000000000003, '10.0.1.200', '45.33.32.156', 49202, 443, 'TCP', 'c2_beacon', 'critical', 'MAL-001', 'Third C2 beacon in 10 minutes -- confirmed periodic callback');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 13:12:00+0000', e4000000-0000-0000-0000-000000000004, '10.0.1.200', '10.0.2.10', 49300, 445, 'TCP', 'lateral_movement', 'critical', 'LAT-001', 'SMB connection from compromised host to internal app server');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 13:14:00+0000', e4000000-0000-0000-0000-000000000005, '10.0.1.200', '10.0.3.20', 49301, 445, 'TCP', 'lateral_movement', 'critical', 'LAT-001', 'SMB connection from compromised host to database server');

INSERT INTO events (event_date, event_time, event_id, source_ip, dest_ip, source_port, dest_port, protocol, event_type, severity, rule_id, description)
VALUES ('2026-03-17', '2026-03-17 14:00:00+0000', e4000000-0000-0000-0000-000000000006, '10.0.1.200', '45.33.32.156', 49203, 443, 'TCP', 'data_exfiltration', 'critical', 'EXFIL-001', 'Large outbound data transfer (450MB) to C2 server');

-- ── Events by Source (denormalized for IP investigation) ──

-- SSH brute force attacker
INSERT INTO events_by_source (source_ip, event_date, event_time, event_id, event_type, severity, dest_ip, dest_port)
VALUES ('203.0.113.42', '2026-03-17', '2026-03-17 10:15:01+0000', e2000000-0000-0000-0000-000000000001, 'auth_failure', 'medium', '10.0.1.5', 22);

Populate events_by_source for investigation

In a production system, your ingestion pipeline would write to both tables simultaneously. For this tutorial, let’s insert the key attack events into the source-IP view:

VALUES ('203.0.113.42', '2026-03-17', '2026-03-17 10:15:03+0000', e2000000-0000-0000-0000-000000000002, 'auth_failure', 'medium', '10.0.1.5', 22);

INSERT INTO events_by_source (source_ip, event_date, event_time, event_id, event_type, severity, dest_ip, dest_port)
VALUES ('203.0.113.42', '2026-03-17', '2026-03-17 10:15:05+0000', e2000000-0000-0000-0000-000000000003, 'auth_failure', 'medium', '10.0.1.5', 22);

INSERT INTO events_by_source (source_ip, event_date, event_time, event_id, event_type, severity, dest_ip, dest_port)
VALUES ('203.0.113.42', '2026-03-17', '2026-03-17 10:15:07+0000', e2000000-0000-0000-0000-000000000004, 'auth_failure', 'high', '10.0.1.5', 22);

-- Compromised internal host
INSERT INTO events_by_source (source_ip, event_date, event_time, event_id, event_type, severity, dest_ip, dest_port)
VALUES ('10.0.1.200', '2026-03-17', '2026-03-17 13:00:00+0000', e4000000-0000-0000-0000-000000000001, 'c2_beacon', 'critical', '45.33.32.156', 443);

INSERT INTO events_by_source (source_ip, event_date, event_time, event_id, event_type, severity, dest_ip, dest_port)
VALUES ('10.0.1.200', '2026-03-17', '2026-03-17 13:12:00+0000', e4000000-0000-0000-0000-000000000004, 'lateral_movement', 'critical', '10.0.2.10', 445);

INSERT INTO events_by_source (source_ip, event_date, event_time, event_id, event_type, severity, dest_ip, dest_port)
VALUES ('10.0.1.200', '2026-03-17', '2026-03-17 14:00:00+0000', e4000000-0000-0000-0000-000000000006, 'data_exfiltration', 'critical', '45.33.32.156', 443);

-- ── Threat Intelligence ──

INSERT INTO threat_intel (indicator_type, indicator_value, threat_name, severity, source, first_seen, last_seen, tags)
VALUES ('ip', '45.33.32.156', 'Cobalt Strike C2', 'critical', 'AlienVault OTX',
    '2026-01-15 00:00:00+0000', '2026-03-17 13:00:00+0000',
    {'cobalt-strike', 'c2', 'apt', 'beacon'});

Add threat intelligence

VALUES ('ip', '203.0.113.42', 'SSH Brute Force Botnet', 'medium', 'AbuseIPDB',
    '2026-02-20 00:00:00+0000', '2026-03-17 10:15:00+0000',
    {'brute-force', 'ssh', 'botnet'});

INSERT INTO threat_intel (indicator_type, indicator_value, threat_name, severity, source, first_seen, last_seen, tags)
VALUES ('ip', '198.51.100.77', 'Reconnaissance Scanner', 'low', 'Shodan',
    '2026-03-10 00:00:00+0000', '2026-03-17 11:30:00+0000',
    {'scanner', 'recon', 'port-scan'});

INSERT INTO threat_intel (indicator_type, indicator_value, threat_name, severity, source, first_seen, last_seen, tags)
VALUES ('domain', 'evil-payload.example.net', 'Malware Distribution', 'high', 'VirusTotal',
    '2026-03-01 00:00:00+0000', '2026-03-16 00:00:00+0000',
    {'malware', 'dropper', 'phishing'});

-- ── Incidents ──

Create an incident

INSERT INTO incidents (incident_id, title, severity, status, created_at, updated_at, assigned_to, related_events, notes)
VALUES (
    f5a10000-0000-0000-0000-000000000001,
    'Active C2 Communication from Workstation 10.0.1.200',
    'critical',
    'investigating',
    '2026-03-17 13:05:30+0000',
    '2026-03-17 14:10:00+0000',
    'analyst-rivera',
    [e4000000-0000-0000-0000-000000000001,
     e4000000-0000-0000-0000-000000000002,
     e4000000-0000-0000-0000-000000000003,
     e4000000-0000-0000-0000-000000000004,
     e4000000-0000-0000-0000-000000000005,
     e4000000-0000-0000-0000-000000000006],
    'Workstation 10.0.1.200 is communicating with known Cobalt Strike C2 at 45.33.32.156 on a 5-minute interval. Lateral movement to app server and DB server observed. 450MB data exfiltration detected. Immediate containment required.'
);

Query your data

View events in a time window

The most common SIEM query: "show me everything that happened in the last hour." Because we partition by date and cluster by time, this is a fast range scan:

-- View events in a time window
SELECT event_time, source_ip, dest_ip, event_type, severity, description
FROM events
WHERE event_date = '2026-03-17'
  AND event_time >= '2026-03-17 13:00:00+0000'

This returns only the events within that hour, in reverse chronological order. You will see the C2 beacons, lateral movement, and data exfiltration — a clear attack timeline.

Investigate a suspicious source IP

When an alert fires on a specific IP, you need all its activity fast. The events_by_source table makes this a single-partition read:

-- Investigate the SSH brute force source IP
SELECT event_time, event_type, severity, dest_ip, dest_port
FROM events_by_source

The results clearly show a pattern: four SSH login failures in rapid succession against the same target, all on port 22. Textbook brute force.

Find high-severity events

During an active incident, analysts need to focus on the most critical events. Pull today’s events and filter for high and critical severity:

  AND event_date = '2026-03-17';

-- Find high-severity events (pull today's events, filter in application)
SELECT event_time, source_ip, dest_ip, event_type, severity, description

In your application layer, filter for severity IN ('high', 'critical'). You will see the SSH brute force detection event, the port scan on RDP, all three C2 beacons, the lateral movement, and the data exfiltration — a clear escalation from reconnaissance to compromise to data theft.

Note
 Why filter in the application? The severity column is not part of the primary key, so filtering on it in CQL would require ALLOW FILTERING. For a day’s worth of events that easily fits in memory, application-side filtering is cleaner. For very high-volume environments, consider a separate events_by_severity table.

Check all activity from the compromised host

Now the critical investigation — what has 10.0.1.200 been doing?

WHERE event_date = '2026-03-17'
LIMIT 50;

-- Check all activity from the compromised host

The timeline tells the whole story: C2 beacons at regular intervals, lateral movement to internal servers, and a large data exfiltration. This host is compromised and actively being used by an attacker.

Look up threat intelligence

Cross-reference the C2 server’s IP against your threat intel database:

FROM events_by_source
WHERE source_ip = '10.0.1.200'
  AND event_date = '2026-03-17';

This confirms the IP is a known Cobalt Strike C2 server, first seen in January, reported by AlienVault OTX. The tags tell you exactly what you are dealing with.

Review the incident

Pull up the full incident record to see the investigation status and all related events:

SELECT threat_name, severity, source, first_seen, last_seen, tags
FROM threat_intel
WHERE indicator_type = 'ip'
  AND indicator_value = '45.33.32.156';

The related_events list gives you direct references back to the individual events in the events table. In your application, you would loop through these UUIDs to build a complete attack timeline.

Correlate: find all events targeting the same destination as a known threat

Let’s see if the SSH brute force attacker (203.0.113.42) was also targeting other hosts. First, confirm what we know about them:

-- Review the incident
SELECT title, severity, status, assigned_to, related_events, notes
FROM incidents
WHERE incident_id = f5a10000-0000-0000-0000-000000000001;

The result confirms this IP is part of a known SSH brute force botnet. This is valuable context — it tells you the attack is automated, not targeted, which changes your response posture.

Search threat intel by type

To see all known malicious IPs in your threat database:

-- Look up threat intel for the SSH brute force IP
SELECT threat_name, severity, tags
FROM threat_intel

This returns all IP-based indicators, sorted by the indicator value. You could feed these into your firewall rules or use them to enrich incoming events in real time.

Look up domain-based threats

  AND indicator_value = '203.0.113.42';

-- Search all IP-based threat intel
SELECT indicator_value, threat_name, severity, tags

Your detection engine can check every outbound DNS query against this table. If a host resolves evil-payload.example.net, you know immediately that it is trying to reach a known malware distribution site.

Update the incident with correlation findings

Good investigations connect the dots. Let’s update the incident to document what we found:

WHERE indicator_type = 'ip';

-- Look up domain-based threats
SELECT indicator_value, threat_name, severity, source, tags
FROM threat_intel
WHERE indicator_type = 'domain';

What you learned

You built a complete security event monitoring platform. Here is what you practiced:

  • Date-partitioned event logs. By partitioning on event_date, time-range queries stay fast regardless of how much historical data you have. Each day is its own partition, which also makes data retention easy — drop old partitions when they expire.

  • Dual-write denormalization. The events and events_by_source tables store the same events optimized for different access patterns. One serves time-range queries, the other serves IP-based investigations. In Ferrosa, you model for the query, not the entity.

  • Compound partition keys. The (source_ip, event_date) partition key on events_by_source prevents any single IP from creating an unbounded partition. Even if one IP generates millions of events over its lifetime, each day’s data is bounded.

  • Threat intel as a lookup table. Partitioning by indicator_type and clustering by indicator_value gives you O(1) lookups for any IOC. Your detection engine can check every incoming event against this table with minimal latency.

  • Collection types for relationships. The list<uuid> on incidents and set<text> on threat intel let you store structured relationships without separate join tables. This keeps your read path simple — one query gets the full incident with all its related event IDs.

  • Incident lifecycle tracking. Using UPDATE to modify incident status and notes shows how Ferrosa handles mutable state alongside append-only event data. The incident evolves while the underlying events are immutable.

From here, you could add a blocked_ips table for automated response, a user_activity table to track authentication patterns per user, or an asset_inventory table to enrich events with context about which hosts are critical.