Chapter 13 — Data systems | Engineering Security operations and blue team

Lesson map

Lesson 13 published. Map, example, EX037.

flowchart TD
  N0["Learning outcomes"]
  N1["13.1 Security data as data"]
  N2["13.2 Schemas"]
  N3["13.3 Joins"]
  N4["13.4 Aggregation"]
  N5["13.5 Indexes and cost"]
  N6["13.6 Data quality"]
  N7["13.7 Retention per table"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7
  N7 --> EX["Worked example"]
  EX --> Q["Exercise EX037"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["13.1 Security data as data"]
  N2["13.2 Schemas"]
  N3["13.3 Joins"]
  N4["13.4 Aggregation"]
  N5["13.5 Indexes and cost"]
  N6["13.6 Data quality"]
  N7["13.7 Retention per table"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX037"]

[Learning outcomes]
    |
    v
[13.1 Security data as data]
    |
    v
[13.2 Schemas]
    |
    v
[13.3 Joins]
    |
    v
[13.4 Aggregation]
    |
    v
[13.5 Indexes and cost]
    |
    v
[13.6 Data quality]
    |
    v
[13.7 Retention per table]
    |
    v
[Worked example] --> [Exercise EX037]

Learning outcomes

You will (1) model security data in tables and documents; (2) write queries that join and aggregate events; (3) design schemas for consistent fields; (4) reason about indexing and query cost.

13.1 Security data as data

A SIEM is a data system: it ingests records, stores them, indexes fields and answers queries. Good data design makes detections faster and more accurate.

Treating the SIEM as a data system brings in database ideas: schemas, indexes, query plans and capacity. A poorly designed table can make a simple question take minutes, which matters when an analyst is mid-incident. Amara learns to read query statistics, such as how many records were scanned to answer each search. Slow searches are then rewritten or indexed.

13.2 Schemas

If one source calls the user field "user", another "username" and a third "acct", every query must remember all three. Normalising fields to a common schema at ingestion — one name for user, source address, host, action and outcome — simplifies rules and reduces mistakes.

Normalising at ingestion means converting each source's fields once, when data arrives, rather than in every query that reads it. Values need the same care: one source may write 'FAIL', another 'failure', another a numeric code. The mapping for each source is documented and tested, because a mistake there silently breaks every rule that relies on the field.

13.3 Joins

Many questions join data: "which hosts did the account that logged in from 203.0.113.14 touch next?" joins web logs with internal authentication logs on username and time.

Joining on username alone can mislead if two systems spell names differently or if the same name belongs to a customer and an employee. Time adds a second key, limiting matches to events close together. Joins are often costly, so engineers filter each side first, for instance to the one suspect address, before combining them.

13.4 Aggregation

Counting, grouping and windowing (for example, per 10 minutes) are the core of threshold detections.

A threshold detection takes raw events, groups them by a key such as source network, counts them within a time window and compares the count with a limit. Fixed windows can split a burst across a boundary so neither half crosses the threshold. Sliding windows avoid that at higher computational cost, a trade-off Amara tests on the portal data. She records how many bursts each window type finds on the same day of data.

13.5 Indexes and cost

Indexed fields are fast to search; unindexed full-text searches are slow and expensive. Engineers index fields that detections and analysts use most, such as user, host and source address.

An index works like the index of a book: it points straight to records holding a given value, instead of reading every page. Each index takes storage and slows ingestion slightly. Choosing indexed fields from the queries analysts actually run, rather than indexing everything, keeps both search speed and storage cost under control.

13.6 Data quality

Missing values, wrong types and duplicates distort results. Quality checks at ingestion catch them early.

A time field parsed in the wrong zone can shift events by hours and break correlations; duplicate events can double counts and trigger thresholds falsely. Ingestion checks count records failing validation per source and alert when the rate jumps, which often signals a format change after a software update on the sending system. Each source has an owner who is told when its rejection rate rises, so the fix happens at the sender rather than through workarounds in every query.

13.7 Retention per table

Different tables may need different retention: authentication logs kept longer than verbose debug logs.

Authentication records help reconstruct account misuse months later, so they earn long retention. Verbose debug output is rarely useful beyond a few days and costs a great deal to keep. Setting retention per table, with reasons recorded, lets the SOC spend storage where investigations actually need it while meeting legal minimums. The retention table is reviewed yearly with the legal team, and any change is recorded with the reason and approver.

Time windows

Windowed aggregation needs care at boundaries: a burst spanning 10:09–10:11 may split across two 10-minute windows and miss a threshold in both.

Worked example

Amara finds the portal logs store the timestamp as local time text while internal logs use UTC. Her join returns nothing because times never match. She adds a parsing step converting portal times to UTC at ingestion, re-runs the join and finds u5's session touched no internal systems.

Synthetic case. Tables and fields are invented.

Errors and limits

Normalisation can lose source detail; keep the original raw record alongside normalised fields.

Exercises

EX037 — Field normalisation. Why normalise field names at ingestion?
Show marking criteria
Rubric: 3 = one name per concept simplifies queries and reduces errors; 2 = partial; 1 = style; 0 = none.
EX038 — Failed join. Why did Amara's join return nothing, and how was it fixed?
Show marking criteria
Rubric: 3 = local-time text vs UTC; convert at ingestion; 2 = partial; 1 = bad query guess; 0 = none.
EX039 — Indexing choice. Which fields should be indexed first?
Show marking criteria
Rubric: 3 = most used by detections and analysts: user, host, source address; 2 = partial; 1 = all; 0 = none.

Synthesis

Treat security data as a designed data system: consistent schemas, careful joins, sensible indexes and quality checks.

Continue studying — chapter 13 of 50

← Chapter 12 (draft) · Course chapter index · Chapter 14 (draft) →