Chapter 13 — Data systems
Lesson map
Lesson 13 published. Map, example, EX037.
flowchart TD
N0["Learning outcomes"]
N1["13.1 Data sources"]
N2["13.2 Schema"]
N3["13.3 Queries"]
N4["13.4 Retention"]
N5["Pseudonymisation"]
N6["Data quality checks"]
N7["Synthesis"]
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 Data sources"]
N2["13.2 Schema"]
N3["13.3 Queries"]
N4["13.4 Retention"]
N5["Pseudonymisation"]
N6["Data quality checks"]
N7["Synthesis"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX037"]
[Learning outcomes]
|
v
[13.1 Data sources]
|
v
[13.2 Schema]
|
v
[13.3 Queries]
|
v
[13.4 Retention]
|
v
[Pseudonymisation]
|
v
[Data quality checks]
|
v
[Synthesis]
|
v
[Worked example] --> [Exercise EX037]
Learning outcomes
You will (1) store flow and log data; (2) query efficiently; (3) manage retention.
13.1 Data sources
The design generates firewall logs, flow records (IPFIX, RFC 7011) and VPN logs. Each needs storage and query.
Useful network data comes from several sources: firewall logs record allowed and denied connections, flow records summarise who talked to whom and how much, DNS logs show which names were looked up, and DHCP logs link addresses to devices over time. Each source answers different questions and has gaps. Engineers list what each source contains, how long it is kept, and whose clocks it uses, because combining sources only works when timestamps can be aligned reliably.
13.2 Schema
Kofi designs a simple table: time, source zone, destination zone, port, action, bytes. Zones rather than raw addresses make queries simpler and reduce personal data.
A schema defines the fields each record holds, their types and their meaning, such as timestamp in UTC, source address, destination address, port, action and bytes. Agreeing a common schema before collecting data lets records from different devices be stored together and queried consistently. Without it, one device's source field may mean something different from another's, and analysis quietly gives wrong answers that nobody notices until an investigation depends on them.
13.3 Queries
Typical queries: denies by zone pair this week; top talkers; new destination ports. Indexes on time and zone pair keep them fast.
Queries turn stored records into answers: how many denied connections came from the guest zone yesterday, or which hosts contacted a server outside working hours. Good queries state their time window and filters explicitly, and their results are checked against a small sample read by hand. Saving useful queries with a short description lets colleagues reuse them during incidents, when there is no time to write and debug new ones from scratch.
13.4 Retention
Logs are kept ninety days, then aggregated summaries are kept longer.
Retention decides how long each type of record is kept. Investigations often begin weeks after the event, so records must last long enough to answer late questions, yet personal data should not be kept longer than needed. Engineers set retention per source from policy, legal requirements and investigation experience, calculate the storage this needs, and automate deletion at the end of the period so that the rule is actually applied rather than forgotten.
Pseudonymisation
Flow records link devices to activity, and staff laptops link to people. Kofi stores device identifiers in pseudonymised form, using a keyed hash so the same device always maps to the same pseudonym without revealing its real name. The key is held by the council, not by Kofi, so only authorised staff can re-identify a device during an investigation. This supports analysis of patterns, such as repeated denied attempts from one device, while limiting personal data visible to analysts and to him as a student.
Data quality checks
Before analysing data, Kofi checks quality: are there gaps in time where a device stopped logging, duplicate records, or impossible values such as negative byte counts? A gap might look like a quiet period and mislead an analysis of traffic patterns. He writes a small script that reports gaps longer than ten minutes per source and counts malformed records. Problems are reported to the council team and noted in his analysis, so conclusions are not drawn from incomplete data. Recording these checks also lets a later analyst repeat the same cleaning steps and compare results fairly.
Worked example
A query grouping denies by zone pair gives the weekly table used in later chapters.
Synthetic case. Data are synthetic.
Errors and limits
Storing raw payloads creates privacy risk without benefit for policy analysis.
Exercises
- EX037 — Schema fields. List six schema fields. Rubric: 3 = six;; 2 = five;; 1 = fewer;; 0 = none.
- EX038 — Zone aggregation. Why record zones rather than addresses? Rubric: 3 = simpler queries, less personal data;; 2 = simpler;; 1 = vague;; 0 = none.
- EX039 — Retention rule. What happens after 90 days? Rubric: 3 = aggregated summaries kept;; 2 = deleted;; 1 = vague;; 0 = none.
Synthesis
A lean schema supports analysis while limiting data held.
Continue studying — chapter 13 of 50
← Chapter 12 (draft) · Course chapter index · Chapter 14 (draft) →