Chapter 4 — Data models | Technologist Digital forensics and malware analysis

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 4 — Data models

Lesson map

Lesson 04 published. Map, example, EX010.

flowchart TD
  N0["Learning outcomes"]
  N1["4.1 Formats"]
  N2["4.2 Fields that matter"]
  N3["Lab — parsing security events"]
  N4["4.3 Reading the result"]
  N5["4.4 Caution"]
  N6["4.5 Normalising"]
  N7["4.6 Time zones"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7
  N7 --> EX["Worked example"]
  EX --> Q["Exercise EX010"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["4.1 Formats"]
  N2["4.2 Fields that matter"]
  N3["Lab — parsing security events"]
  N4["4.3 Reading the result"]
  N5["4.4 Caution"]
  N6["4.5 Normalising"]
  N7["4.6 Time zones"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX010"]

[Learning outcomes]
    |
    v
[4.1 Formats]
    |
    v
[4.2 Fields that matter]
    |
    v
[Lab — parsing security events]
    |
    v
[4.3 Reading the result]
    |
    v
[4.4 Caution]
    |
    v
[4.5 Normalising]
    |
    v
[4.6 Time zones]
    |
    v
[Worked example] --> [Exercise EX010]

Learning outcomes

You will (1) understand structured data formats used in logs (CSV, JSON); (2) parse events with the standard library; (3) count and filter events; (4) recognise patterns such as repeated failures followed by success.

4.1 Formats

CSV holds rows with named columns; JSON holds nested objects. Many security tools export one or the other. The data model — which fields exist, their types and meaning — must be understood before analysis.

Before parsing an export, Jonah opens it in a text viewer and writes down each column's name, an example value and what he believes it means, then checks his reading against the tool's documentation. A column called time may hold the event time, the time the log was written or the time it was exported; mixing them up shifts a whole timeline without any error appearing.

4.2 Fields that matter

Time (with zone), host, event type, user, source address, result. Missing or ambiguous fields limit what can be concluded.

In the lab data, every row has a UTC time, the host CLERK-03, an event type and a user, but no source address. That gap matters: the events show that the local account admin was used, not from where. Jonah notes the missing field in his report so nobody reads more into the six rows than they can support. The source address arrives later, from a different log.

Lab — parsing security events

"""Parse synthetic CSV security events and count by type (stdlib csv)."""
import csv, io, collections
data = """time_utc,host,event,user
2026-05-04T07:58:00Z,CLERK-03,logon_success,jdiaz
2026-05-04T08:01:12Z,CLERK-03,logon_failure,admin
2026-05-04T08:01:15Z,CLERK-03,logon_failure,admin
2026-05-04T08:01:19Z,CLERK-03,logon_failure,admin
2026-05-04T08:02:40Z,CLERK-03,logon_success,admin
2026-05-04T08:05:03Z,CLERK-03,service_installed,admin
"""
rows = list(csv.DictReader(io.StringIO(data)))
print(collections.Counter(r["event"] for r in rows).most_common())
print("first success after failures:", next(r["time_utc"] for r in rows[4:] if r["event"] == "logon_success"))

Run with python3 lab_events.py. Verified output:

[('logon_failure', 3), ('logon_success', 2), ('service_installed', 1)]
first success after failures: 2026-05-04T08:02:40Z

4.3 Reading the result

Six synthetic events from CLERK-03: three logon failures for "admin", two successes, one service installation. The first success after the failures is at 08:02:40 — for "admin", 88 seconds after the first failure at 08:01:12 — followed by a service installed at 08:05:03 under the same account. The pattern (failures, then success, then a system change) is a classic lead for password guessing followed by misuse.

Jonah writes his summary in that order, observations first and interpretation second, so a reader can separate what the log recorded from what he concludes. He also keeps the script and its output with the case notes, which lets Renata rerun it and see the same counts.

4.4 Caution

Three failures could also be a user mistyping. The service installation and the account involved (a local administrator not normally used) make the sequence more concerning; further evidence is needed.

Jonah lists what would help decide: whether IT staff used the account that morning, whether the service has a known purpose, and where the logon came from. Each is a question for a specific source. Writing the alternative explanation next to the concerning one keeps the report honest and tells Renata which checks to request.

4.5 Normalising

Events from different tools use different field names; mapping them into one model allows combining sources.

At Alder Falls, one tool calls the account field user, another account_name and a third TargetUserName. Jonah keeps a small mapping table in the repository listing each source field and the common name it maps to, with the date it was checked. When a tool changes its export format, the table shows exactly which mapping must be updated. The original field names stay in the raw export, so the mapping can always be checked against it.

4.6 Time zones

The Z suffix means UTC. Fields without zones must be checked against device settings.

A time without a zone is ambiguous by hours. If a device logs local time and another logs UTC, a merged timeline can place a logon after the action it enabled. Jonah converts everything to UTC, records the original value and the zone he assumed, and checks the assumption against the device's configuration before relying on the order of events. The lab data use the Z suffix throughout, so no conversion was needed there; real city exports are rarely so tidy.

4.7 Preserving originals

Parsed outputs are derived data; original exports are kept and hashed.

Jonah's practice is to hash each export when it arrives, store it read-only, and run scripts only against a working copy. If someone later questions a count, the original can be rehashed to show it is unchanged and the script rerun to show how the number was derived. A parsed table without its source cannot be checked by anyone. Derived tables are labelled with the export they came from and the script version that produced them.

Worked example

Jonah reports: "On CLERK-03, three failed logons for local account 'admin' (08:01:12–08:01:19 UTC) were followed by a successful logon at 08:02:40 and a service installation at 08:05:03 under the same account." Renata adds a note to check whether 'admin' is used by IT staff.

Synthetic case. Events come from the lab script.

Errors and limits

Event logs record what auditing was configured to record; absent events may simply not have been logged.

Exercises

EX010 — Lab counts. Report the event counts and the first success after failures. Rubric: 3 = 3 failures, 2 successes, 1 service; 08:02:40; 2 = partial; 1 = wrong; 0 = none.
EX011 — Pattern meaning. Why is the sequence concerning but not conclusive? Rubric: 3 = fits guessing + misuse; could be mistyping; needs more evidence; 2 = partial; 1 = proves attack; 0 = none.
EX012 — Normalising. Why normalise field names across tools? Rubric: 3 = combine sources in one model; 2 = partial; 1 = style; 0 = none.

Synthesis

Understand the data model, parse carefully, look for sequences and keep originals.

Continue studying — chapter 04 of 30

← Chapter 03 (draft) · Course chapter index · Chapter 05 (draft) →