Chapter 13 — Data systems | Engineering Threat intelligence and OSINT

Open, free learning for anyone who wants to protect people and technology. No account is required to read this text or its worked answers.

Chapter 13 — Data systems

Lesson map

Lesson 13 published. Map, example, EX037.

flowchart TD
  N0["Learning outcomes"]
  N1["13.1 Normalisation"]
  N2["Lab 4 — normalise and de-duplicate"]
  N3["13.2 Reading the result"]
  N4["13.3 Keys and indexes"]
  N5["13.4 Provenance"]
  N6["13.5 Retention"]
  N7["Practice note — Query patterns"]
  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 Normalisation"]
  N2["Lab 4 — normalise and de-duplicate"]
  N3["13.2 Reading the result"]
  N4["13.3 Keys and indexes"]
  N5["13.4 Provenance"]
  N6["13.5 Retention"]
  N7["Practice note — Query patterns"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX037"]

[Learning outcomes]
    |
    v
[13.1 Normalisation]
    |
    v
[Lab 4 — normalise and de-duplicate]
    |
    v
[13.2 Reading the result]
    |
    v
[13.3 Keys and indexes]
    |
    v
[13.4 Provenance]
    |
    v
[13.5 Retention]
    |
    v
[Practice note — Query patterns]
    |
    v
[Worked example] --> [Exercise EX037]

Learning outcomes

You will (1) normalise and de-duplicate indicators before storage; (2) choose keys and indexes for intelligence queries; (3) model provenance for every stored object; (4) design retention and deletion.

13.1 Normalisation

The same domain can arrive as C2.Example., c2.example or defanged text. Without normalisation the store fills with duplicates and match counts lie.

The lab shows the effect: seven inputs, four real values. A match count based on raw strings would claim BEN saw seven distinct indicators when it saw four. Kofi's normaliser lowers case where case does not matter, removes a trailing dot from domains, strips spaces and refangs text, each step tested on its own. The order of the steps is fixed and documented.

Lab 4 — normalise and de-duplicate

"""Lab 4 (ch 13): normalise and de-duplicate synthetic indicators before storage."""
raw = ["C2.Example.", "c2.example", "hxxp://drop[.]example/a", "http://drop.example/a",
       "203.0.113.9", " 203.0.113.9 ", "198.51.100.44"]
def norm(s):
    s = s.strip().lower().replace("hxxp", "http").replace("[.]", ".")
    return s[:-1] if s.endswith(".") else s
seen, out = set(), []
for r in raw:
    n = norm(r)
    if n not in seen:
        seen.add(n); out.append(n)
print(f"raw={len(raw)} unique={len(out)}")
for o in out:
    print(" ", o)

Run with python3 lab_dedupe.py. Verified output:

raw=7 unique=4
  c2.example
  http://drop.example/a
  203.0.113.9
  198.51.100.44

13.2 Reading the result

Seven raw strings collapse to four unique values. The trailing dot, capitals, defanging and surrounding spaces were all noise. Normalisation rules must be documented, versioned and idempotent.

Kofi treats the rules as part of the data model. Each rule has a written reason, a test, and a version number stored with every record it produced. Running the normaliser on its own output must give the same result, which the property tests in the programming chapter check. If a rule changes, older records can be found by version and reprocessed, rather than leaving two different normal forms in the store at once.

13.3 Keys and indexes

STIX 2.1 objects carry an id of the form type--UUID. Lantern keys objects by that id and indexes normalised indicator values for fast matching. Sightings are keyed by indicator id and time.

The identifier never changes, even if the indicator's description or confidence does, so references from relationships and sightings stay valid. Indexes are chosen from the questions analysts ask most. Kofi measured matching speed with and without the value index and found the difference large enough to justify its storage cost. Rarely used queries can run more slowly instead of having their own indexes.

13.4 Provenance

Each object records its source, TLP label, collection time and parser version. Duplicate indicators from two sources are stored once with both provenance entries, so analysts see corroboration.

Corroboration only counts if the sources are independent. If partner B simply copies partner A's feed, two entries add nothing. Kofi's model records where each partner says it obtained an indicator when the partner provides that, so analysts can spot repeated rather than independent reporting. The most restrictive label of any source travels with the shared record, so merging never relaxes a sharing limit.

13.5 Retention

Retention follows agreements and law. Deletion must reach backups within a stated period; "deleted from the UI" is not deletion.

Kofi's design gives each source a retention period from its agreement. A scheduled job removes expired records from the live store, and backups are destroyed on a fixed cycle, so an expired record survives in backup copies for no longer than the stated period. The job writes a log of counts removed per source, which the privacy officer reviews. If a partner asks for deletion, the same log shows when it was done.

Practice note — Query patterns

Analysts ask three kinds of question: does this value exist, what is related to it, and what changed recently. Exact-match lookups need hash indexes on normalised values; relationship queries need indexes on source and target ids; recency queries need time indexes. Designing indexes from real questions avoids both slow queries and wasted storage. Bulk deletions are rehearsed on a copy first, because a mistaken normalisation rule applied during deletion can remove far more than intended.

Worked example

Kofi merges two partners' feeds. Normalisation finds that 18% of partner B's indicators already exist from partner A. Rather than discard them, Lantern adds partner B's provenance to the existing records, raising their corroboration count.

Synthetic case. Feeds and percentages are invented.

Errors and limits

Over-aggressive normalisation can merge distinct values (for example case-sensitive URL paths). Test rules against real formats.

Exercises

EX037 — Unique count. How many unique values does the lab produce from seven inputs? Rubric: 3 = 4; 2 = correct with one wrong listed value; 1 = 7; 0 = none.
EX038 — Corroboration. Why keep both provenance entries for a duplicate indicator? Rubric: 3 = shows corroboration from independent sources; audit; 2 = audit only; 1 = discard duplicates; 0 = none.
EX039 — Path case. Give an example where lower-casing a whole URL could be wrong. Rubric: 3 = case-sensitive path/query on server; 2 = vague; 1 = never wrong; 0 = none.

Synthesis

Normalise carefully, key by stable ids, keep provenance, and make deletion real.

Continue studying — chapter 13 of 50

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