--- title: 'Audit Log Design' source: 'https://academia.sh/en/courses/application-detection-and-compliance/audit-log-design' course: 'Detection, Response and Compliance' language: en updated: '2026-10-07T04:31:05+00:00' license: 'CC BY-SA 4.0' objectives: - 'Matches a question set against a log schema and splits each unanswered question into three causes: no field, no join key, retention window too short' - 'Calculates what each added log field costs in daily volume, retention days, and personal data items' - 'Shows by running the code the point at which more records answer fewer questions under a fixed budget' --- # Audit Log Design Matched against three log sources, a set of sixteen questions leaves six answered and ten unanswered: four because the field is not written, five because no join key links the sources, one because of the retention window. Three added fields raise the answered count to 13, but once all five are added the daily volume grows 1.45 times, retention under the fixed budget falls from 65 days to 45, and the five questions that reach into the 60-day audit period go unanswered again, so the answered count drops to 10. The Runtime Security and Secrets Management course built the layers and wrote down what each layer misses. The existence of a missed item was read there off the model's ground truth. A real organization has no such ground truth: what was missed becomes known only from a record. This course measures that record and asks a single question — how many of the questions asked after an incident can be answered. The unit of measurement is the **unanswered question**. A log design is not called "comprehensive" or "inadequate"; a question set is put in front of it, the matching is run, and the questions left without an answer are counted. The second half of the same measure is cost: how many added fields, how many times the volume, and how many personal data items it takes to make them answerable. This lesson runs the two together. How a log is written, kept immutable, and protected for integrity was measured in the Server Security and Going to Production course; it is not repeated here, it becomes an input. - **DR1.** The example network is **fictional**: an unauthorized access breach in the subscriber portal of a regional measurement network, nine services, three log sources, and a 60-day audit period. Every number is the output of a model. - **DR2.** The **question set** is the questions asked after an incident, modeled as a data structure. Each question has two attributes: the **fields** needed to answer it and how many days back it reaches. - **DR3.** A question is answered only if all of its required fields lie within a **single joinable field set**. Fields in two separate sources cannot be joined unless they share a join key; the two records sit side by side, but it cannot be shown that they belong to the same event. - **DR4.** Being unanswerable splits into three causes: **no field** (no source writes it), **no join key** (the fields exist but do not match), **window** (the fields and the key exist but the log is not retained that far back). - **DR5.** Retention is determined not by a decision but by arithmetic: a fixed retention budget divided by the daily volume. In the model the budget is 600 GB and the daily event count is 42 million; 1 GB counts as 10⁹ bytes. - **DR6.** Personal data is counted **by type**: the client address, the session identifier, and the field value from before a change are one item each. Each item places a separate upper limit on retention. ## Matching the Question Set to the Schema ```js // record-matching.mjs — matches the question set to the log schema; the unanswered question // is the unit of measurement of this course. A MODEL: the network, logs, and questions are fictional. export const BUDGET_B = 600e9, DAILY_EVENTS = 42e6; // fixed retention budget and daily events // Source = one log stream. Sources that have a `trace` field can be joined to each other; // a source without it stands alone and its fields cannot be combined with other fields. export const SOURCES = [ { name: "application audit log", share: 0.18, bytes: 240, fields: ["time", "trace", "actor", "action", "outcome"] }, { name: "network gateway access log", share: 0.62, bytes: 180, fields: ["time", "trace", "path", "method", "status", "client_ip"] }, { name: "database change log", share: 0.20, bytes: 320, fields: ["time", "table", "object", "operation"] }, ]; // Question = [text, required fields, how many days back it reaches]. The audit period is 60 days. export const QUESTIONS = [ ["which account read which subscriber record", ["actor", "object", "action"], 30], ["was the read accepted or denied", ["actor", "action", "outcome"], 30], ["which client address did the request come from", ["client_ip", "trace"], 7], ["which account changed which table", ["actor", "table", "operation"], 30], ["what was the value before the change", ["table", "old_value"], 30], ["which application version was running", ["actor", "action", "version"], 30], ["what else was done in the same session", ["session", "action"], 7], ["what was the authorization decision and its basis", ["actor", "action", "authz_decision"], 30], ["when was the first denial at the start of the period", ["actor", "action", "outcome"], 60], ["how many subscriber records were read in the period", ["object", "action"], 60], ["which endpoints were called", ["path", "method", "status"], 30], ["which request changed which row", ["trace", "table", "object"], 60], ["how many accounts were tried from the same address", ["client_ip", "actor"], 30], ["what did the same account do in the quarter before the incident", ["actor", "action"], 90], ["what record set was exported", ["actor", "action", "object", "outcome"], 60], ["how many rows changed in the period", ["table", "operation"], 60], ]; // A question is answered only if ALL of its required fields are inside ONE joinable // cluster; fields in separate clusters cannot be combined. export function clusters(src) { const linked = src.filter((s) => s.fields.includes("trace")); const alone = src.filter((s) => !s.fields.includes("trace")); const merged = [...new Set(linked.flatMap((s) => s.fields))]; return [merged, ...alone.map((s) => s.fields)].filter((f) => f.length); } export function match(src, retention) { const cluster = clusters(src), all = new Set(src.flatMap((s) => s.fields)); return QUESTIONS.map(([text, fields, days]) => { const missing = fields.filter((f) => !all.has(f)); if (missing.length) return { text, days, status: "no field", missing: missing[0] }; if (!cluster.some((c) => fields.every((f) => c.includes(f)))) return { text, days, status: "no join key", missing: "-" }; if (days > retention) return { text, days, status: "window", missing: "-" }; return { text, days, status: "answered", missing: "-" }; }); } export const dailyBytes = (src) => src.reduce((t, s) => t + DAILY_EVENTS * s.share * s.bytes, 0); export const retentionDays = (src) => Math.floor(BUDGET_B / dailyBytes(src)); export const gen = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); if (import.meta.url === `file://${process.argv[1]}`) { const gb = dailyBytes(SOURCES) / 1e9, retention = retentionDays(SOURCES); console.log(`model: ${SOURCES.length} sources, ${QUESTIONS.length} questions, ` + `${gb.toFixed(2)} GB daily, ${BUDGET_B / 1e9} GB budget -> ${retention} days retention`); console.log(`joinable field sets: ` + clusters(SOURCES).map((c) => c.length).join(" and ") + " fields"); const y = gen([64, 6, 13, 15]); console.log("\n" + y(["question", "day", "status", "missing field"])); for (const s of match(SOURCES, retention)) console.log(y([s.text, s.days, s.status, s.missing])); const d = match(SOURCES, retention); const count = (t) => d.filter((s) => s.status === t).length; console.log(`\nanswered ${count("answered")}, unanswered ` + `${QUESTIONS.length - count("answered")}: no field ${count("no field")}, ` + `no join key ${count("no join key")}, window ${count("window")}`); } ``` ``` model: 3 sources, 16 questions, 9.19 GB daily, 600 GB budget -> 65 days retention joinable field sets: 9 and 4 fields question day status missing field which account read which subscriber record 30 no join key - was the read accepted or denied 30 answered - which client address did the request come from 7 answered - which account changed which table 30 no join key - what was the value before the change 30 no field old_value which application version was running 30 no field version what else was done in the same session 7 no field session what was the authorization decision and its basis 30 no field authz_decision when was the first denial at the start of the period 60 answered - how many subscriber records were read in the period 60 no join key - which endpoints were called 30 answered - which request changed which row 60 no join key - how many accounts were tried from the same address 30 answered - what did the same account do in the quarter before the incident 90 window - what record set was exported 60 no join key - how many rows changed in the period 60 answered - answered 6, unanswered 10: no field 4, no join key 5, window 1 ``` All three sources write regularly, none is switched off, and 9.19 GB of records are produced per day. Even so, ten of the sixteen questions have no answer. The real information in this table is not the number of unanswered questions but the **distribution of causes** in the third column, because the three causes are fixed by three different jobs and their costs differ. The largest cluster is in the **no join key** rows: five questions. In these questions, all of the required fields are written. The `actor` that the question "which account changed which table" needs sits in the application audit log, and `table` and `operation` sit in the database change log. The two records may have been written in the same second, but because the database log has no `trace` field, it cannot be shown which request caused which row change. In a busy period there are hundreds of requests and hundreds of row changes in the same second; a join based on timestamps produces a claim, not evidence. **Two log sources with no join key answer only as many questions as each collects on its own, not as many as their sum.** The second cluster is **no field**: four questions. These are design gaps and are closed by writing new fields. The third is a single row but the most insidious: `window`. The question "what did the same account do in the quarter before the incident" has all of its fields and its join key; it goes unanswered because it reaches back 90 days. This question can never be found by looking at the schema. The schema is right, the fields are complete, the join key seems established — unanswerability comes from the retention arithmetic and surfaces only when the question set is modeled together with the window. ## What Making Questions Answerable Costs Four missing fields and one join key can be written. Each added field enters every record and grows the daily volume; because the budget is fixed, volume turns directly into retention days. ```js // additions.mjs — each added field raises the answered count but grows the daily volume; // under a fixed budget volume becomes retention days, and retention days feed back into answers. A MODEL. import { SOURCES, QUESTIONS, match, dailyBytes, retentionDays, gen } from "./record-matching.mjs"; // Each addition: which field goes to which sources, how many bytes per record, personal data or not. const ADDITIONS = [ { field: "trace", targets: ["database change log"], bytes: 18, personal: false }, { field: "authz_decision", targets: ["application audit log"], bytes: 16, personal: false }, { field: "version", targets: ["application audit log"], bytes: 12, personal: false }, { field: "session", targets: ["application audit log", "network gateway access log"], bytes: 26, personal: true }, { field: "old_value", targets: ["database change log"], bytes: 340, personal: true }, ]; const apply = (src, a) => src.map((s) => a.targets.includes(s.name) ? { ...s, bytes: s.bytes + a.bytes, fields: [...s.fields, a.field] } : { ...s }); const BASE_GB = dailyBytes(SOURCES) / 1e9; const y = gen([16, 10, 7, 11, 12, 14, 9]); console.log(y(["added field", "daily GB", "ratio", "retention", "answered", "unanswered", "personal"])); let src = SOURCES.map((s) => ({ ...s })), personal = 1; // client_ip is already personal const row = (name) => { const gb = dailyBytes(src) / 1e9, ret = retentionDays(src); const c = match(src, ret).filter((s) => s.status === "answered").length; console.log(y([name, gb.toFixed(2), (gb / BASE_GB).toFixed(2), ret, c, QUESTIONS.length - c, personal])); return { ret, c }; }; const trail = [row("(base schema)")]; for (const a of ADDITIONS) { src = apply(src, a); if (a.personal) personal++; trail.push(row(a.field)); } const best = trail.reduce((a, b) => (b.c > a.c ? b : a)), last = trail.at(-1); console.log(`\nbest schema retains ${best.ret} days: ${best.c} questions`); console.log(`last schema answers ${best.c - last.c} fewer questions, because retention fell from ` + `${best.ret} days to ${last.ret} and the audit period is 60 days`); console.log(`base schema to last schema: answered ${trail[0].c} -> ${last.c}, ` + `volume ${(dailyBytes(src) / 1e9 / BASE_GB).toFixed(2)}x, personal data 1 -> ${personal} items`); ``` ``` added field daily GB ratio retention answered unanswered personal (base schema) 9.19 1.00 65 6 10 1 trace 9.34 1.02 64 11 5 1 authz_decision 9.46 1.03 63 12 4 1 version 9.55 1.04 62 13 3 1 session 10.43 1.13 57 9 7 2 old_value 13.28 1.45 45 10 6 3 best schema retains 62 days: 13 questions last schema answers 3 fewer questions, because retention fell from 62 days to 45 and the audit period is 60 days base schema to last schema: answered 6 -> 10, volume 1.45x, personal data 1 -> 3 items ``` The first addition is the cheapest in the table and gains the most: an 18-byte `trace` field in the database log grows the daily volume by 2% and raises the answered count from 6 to 11. On its own it records no new fact; it only joins two records that were already being written. **In a log design, the highest-yield field is often not a new fact but the field that says two existing facts belong to the same job.** The next two additions progress linearly: `authz_decision` and `version` grow the volume by 4% and raise the answered count to 13. This row is the best point in the table and cannot be found without looking at the table. The **order** of the added fields is not arbitrary either: each field in the list brings in volume equal to its bytes per record times the daily share of the source it enters. The `version` field is 12 bytes but enters only the source that carries 18% of the events; `session` is 26 bytes and enters 80% of them. The second seems to take a little more than twice the space of the first; in reality it takes more than nine times as much. **A field's cost is measured not by its bytes but by the number of records its bytes are written to.** The fourth row shows the break. The `session` field enters two high-volume sources and the daily volume rises to 10.43 GB; under the fixed budget retention falls from 62 days to 57. This addition makes one question answerable and **leaves the five questions that reach 60 days unanswered all at once**: the answered count drops from 13 to 9. In the fifth row, writing the value from before the change raises the volume 1.45 times, lowers retention to 45 days, and the table closes at 10 — only four questions better than the base schema, three worse than the best schema. This is the name of what stands as a single curve in the table: **more records, fewer answers under a fixed budget.** The break happens not because the log volume grew but because the retention window fell below how far the question set reaches. The audit period is 60 days; a 57-day retention leaves the first three days of the period unanswered even if the log itself is flawless. The third column of the cost looks single-digit in the table but is the longest-lived. The personal data items go from 1 to 3: the session identifier and the field value from before the change are items that can be tied directly to the subscriber's identity. These two fields do not only bring volume; they restrict access to the record, put a separate upper limit on retention, and require them to be scrubbed from every evidence copy handed out. The cost of writing a field "just in case" is read across these three columns at once. ## Summary - A log design is measured not by labeling but by the question set put in front of it; the unit of measurement is the unanswered question. In the model, 10 of 16 questions are unanswered with the base schema. - Being unanswerable splits into three distinct causes, and the three are fixed by three distinct jobs: no field (4 questions), no join key (5 questions), retention window too short (1 question). - The highest-yield field is not a new fact but the join key that says two records belong to the same job: for 18 bytes of volume, the answered count rises from 6 to 11. - Under a fixed retention budget, each added field turns into retention days: five fields raise the volume 1.45 times and lower retention from 65 days to 45. - More records can give fewer answers: the best schema answers 13 questions with 62 days of retention, while the schema with two more fields stays at 10 questions with 45 days, because the audit period is 60 days. - The lasting column of the cost is the personal data item: this number, rising from 1 to 3, brings an access restriction, a separate retention limit, and a scrubbing burden on every evidence copy. ## Next Step All of this lesson's measurement was made **after** the incident: the question had already been asked, and the log was checked for whether it held an answer. The same records have a second job — announcing the incident that gave rise to the question while it is still going on. But an abuse at the application level does not resemble an attack at the protocol level: the requests are valid, the session is open, the authorization decision is mostly positive, and the only anomaly is in a number that a business rule measures. The next lesson runs detection scenarios built on these numbers over an event stream and measures the two costs of choosing a threshold together: how many abuse sessions are missed and how many times someone is woken up for nothing.