1. Courses
  2. Cybersecurity
  3. Defensive Security: SOC, Detection and Response
  4. Stop 2

30 minIntermediate

Logs that matter

You cannot detect what you do not log, but logging everything drowns you in noise and cost. This lesson shows you how to choose log sources by the questions you need to answer, pull the high-value events from identity, endpoint, network, cloud and application logs, put time and fields into one shape, and query them fast enough to use in a real investigation.

By the end you can

  • Pick log sources from the detection questions you need to answer, and spot coverage gaps
  • Name the high-value events in identity, endpoint, network, cloud and application logs
  • Keep time trustworthy: UTC everywhere, event time versus ingest time, and late events
  • Normalise fields to one common schema so a query works across every source
  • Query logs fast by filtering on time and source first, then pivoting across events

The everyday picture

Think of the cameras in a Mangrove Market store. If no camera points at the stockroom door, you will never see who took the missing crates, no matter how hard you look afterwards. So coverage comes first: you point cameras at the doors, the tills and the stockroom, the places where something worth watching happens.

But you cannot record every corner of every store at full quality and keep it forever. The storage fills up, and when something goes wrong you have hours of footage and no way to find the clip. So the manager does three more things. Every camera stamps its footage with the same clock, so clips from different cameras line up. The till-area footage is kept longer than the quiet-aisle footage. And to find a theft, you jump straight to the time and the camera first, then watch, instead of scrubbing through everything. Security logging is the same job: cover the places that matter, put every source on one clock, keep the important streams longer, and search by time and source before anything else.

Log what answers a question

The instinct when you first run a is to send it everything. It feels safe, but it is a trap. Every gigabyte costs money to store and index, every noisy source buries the events you need, and a query over a year of debug logs times out when you are mid-incident. The useful question is not "what can we collect?" but "what do we need to answer?"

Start from the questions an investigation asks. Who signed in, from where, and did they pass a second factor? What program started this process, and what was its parent? Did this host talk to an unfamiliar server? Who changed a setting in the cloud account at 2 a.m.? Each question points at one source and a few high-value events inside it. Everything else in that source is background you can sample or drop.

SourceHigh-value eventsQuestion it answers
Identity (the login system)Sign-ins and failures, MFA prompts, password resets, privilege and group changesWho is this account, from where, and did it really prove itself?
Endpoint / EDRProcess creation with command line and parent, new services, autostart and persistenceWhat ran on this machine, and what started it?
NetworkFirewall allow and deny, DNS queries, web proxy requestsDid this host talk to something it should not?
CloudControl-plane audit events (a new user, a changed policy), storage accessWho changed the environment, and who read the data?
Application (the Partner app)Sign-in, admin actions, bank-detail and credit-limit changesWhat happened inside the app that network logs cannot see?

From this you write a logging baseline: the short list of events every Mangrove host and app must send, no matter what. Every server and store PC sends process creation and sign-in events; every app sends auth and admin actions; the cloud account sends its full audit trail. Then you look for , the places with no camera. The classic Mangrove gap is the back-office PCs in stores: nobody was shipping their process and sign-in logs, so an attacker on one of them was invisible. A gap is worse than a noisy source, because a noisy source you can tune, but a gap you cannot query at all. Inside a SOC set out who reads these; here we decide what reaches them.

Quick check

Mangrove's SIEM bill is too high, so the team wants to cut volume. Which cut is safest?

Identity sign-ins Endpoint process Network DNS, fw Cloud audit App admin Normalise to one schema same names for every source SIEM event store indexed, searchable one query Fields shared by every row: @timestamp host.name user.name event.action source.ip outcome filter once, pivot across all
Each source speaks its own dialect. Normalising into one common schema, with the same field names for every source, lets a single query filter and pivot across identity, endpoint, network, cloud and app logs at once.

One schema so a query works everywhere

Raw logs are a tower of Babel. The login system calls the account UserPrincipalName, the firewall calls it src_user, the Partner app calls it actor_email, and the cloud audit trail calls it userIdentity.arn. If you leave them that way, every search and every rule has to know all the dialects, and a pivot from a sign-in to a process is hand-written each time.

The fix is a : one agreed set of field names that every source is mapped into as it arrives. Two public schemas are worth knowing. The Elastic Common Schema (ECS) uses dotted names such as user.name, source.ip, event.action and @timestamp. The Open Cybersecurity Schema Framework (OCSF) is a vendor-neutral effort doing the same job. You do not have to invent your own; pick one and map every source to it.

Source and its fieldCommon schema field
Login system UserPrincipalNameuser.name
Firewall src_ipsource.ip
Partner app actor_emailuser.name
EDR CommandLineprocess.command_line
Cloud audit eventNameevent.action

Now one query can ask "show every event where user.name is r.khan, newest first" and get sign-ins, processes, firewall hits and app actions in a single list. That is what makes fast: you follow one account or one host across sources by the same field name, instead of translating in your head. Normalise at collection time, not at search time, so the slow work happens once and every later query is cheap. You will lean on this schema again when you write portable rules in Writing detections.

Quick check

A new store device sends its logs, but its user field arrives as login_id and is never mapped into the schema. What breaks?

Time is the backbone

Every investigation is a timeline, so time has to be trustworthy before anything else is. Three rules keep it that way. Store and display everything in UTC, so a sign-in in one city and a process on a server in another line up without mental arithmetic. Keep every clock in sync with a time service (NTP); a store PC whose clock drifts ten minutes puts its events in the wrong place and hides the link to everything else. And record who observed the event, not just when.

The subtle trap is that an event has two times. is when it happened on the device. is when it reached the SIEM. Most of the time they are seconds apart, so people forget the difference. But a POS till that lost its network in a monsoon storm buffers its logs and sends them hours later, when it reconnects. The sign-in happened at 02:00; the SIEM only saw it at 09:00. This is the same split a product tracking plan draws between when something happened and when it arrived.

Late events change how you search. If you ran a rule at 03:00 that read ingest time, the 02:00 sign-in was not there yet and the rule missed it. So detection and hunting search by event time, and you re-run searches over a window that has already passed to catch events that arrived late. A row that lands in the store seven hours after it happened is not a bug; it is normal, and your queries have to expect it.

02:00 03:00 09:00 normal event happens ingested seconds later late event happens 02:00 ingested 09:00 query window 02:00-03:00
A query run at 03:00 over the dashed window catches the normal event but not the late one, whose row only arrives at 09:00. Search by event time and re-run past windows so late arrivals are not lost.

Retention, cost and logs as evidence

Logs are expensive, so you split storage into tiers. Hot storage is fast to search and holds the recent window you query every day, often the last 30 to 90 days. Cold storage is cheap and slow and holds older logs you rarely touch but must keep, often for a year or more. The split follows the question: a triage analyst works the last few days, while a breach is often found months after it started, so identity, EDR and cloud audit logs earn a long retention, and chatty debug logs do not.

Logs are also evidence. If an incident ends in a dispute, an insurance claim or a court, the logs are what prove the story, so their integrity matters. Keep them on storage the people being investigated cannot edit (write-once or append-only), limit who can delete them, and record when each log was collected. An attacker who can quietly clear logs erases the very record you would use to catch them, which is why log access is itself a high-value event worth watching.

Quick check

Finance asks Mangrove to keep only 14 days of logs to save money. The SOC pushes back. What is the strongest reason?

Querying fast

When an alert fires you have minutes, not hours, so the order of a query matters as much as its logic. Filter on time and source first: a narrow event-time window and the one or two sources that could answer the question shrink billions of rows to thousands before any other work. Then pivot on the fields you indexed, the ones you follow from event to event: user.name, host.name, source.ip. Searching an indexed field is a jump; searching unindexed free text is a scrub through everything.

Then aggregate, then drill in. Count sign-ins per account per hour, or processes per host, to find the handful that stand out, and only then read the raw events for those few. Pulling raw rows first and reading them by eye is how an investigation runs out of time. The practice exercise below is a real pivot: finding successful sign-ins that no MFA challenge protected, the exact gap that let an attacker into a store PC in Thinking like an attacker.

Build it

Build the three things a SOC needs before it can query: a logging baseline, a unified event table in the common schema, and a fast query over it.

  1. Write the logging baseline

    List the events every host and app must send, with a retention tier for each. This is the contract; a source that cannot meet it is a coverage gap to raise.

    Markdown
    # Mangrove logging baseline (every host and app)
| Source    | Must send                                  | Tier (hot days) | Keep |
|-----------|--------------------------------------------|-----------------|------|
| Identity  | sign-in, MFA, reset, privilege change      | 90              | 1 yr |
| Endpoint  | process creation (+ parent, command line)  | 90              | 1 yr |
| Network   | DNS query, firewall allow/deny, proxy      | 30              | 90 d |
| Cloud     | full control-plane audit, storage access   | 90              | 1 yr |
| App       | sign-in, admin action, bank/credit change  | 90              | 1 yr |
All events in UTC, clock synced to NTP, log store append-only.

  2. Create the unified event table in the common schema

    Every source is mapped into these columns as it arrives, so one table answers questions across all of them. Index the fields you pivot on and the event time.

    SQL
    CREATE TABLE event (
  id             bigint PRIMARY KEY,
  event_time     timestamptz NOT NULL,   -- when it happened, UTC
  ingest_time    timestamptz NOT NULL,   -- when the SIEM saw it
  source         text NOT NULL,          -- identity | endpoint | network | cloud | app
  host_name      text,
  user_name      text,                   -- mapped from every source's own field
  source_ip      inet,
  event_action   text,                   -- signin | process_start | dns_query | ...
  outcome        text,                   -- success | failure
  detail         jsonb                   -- the fields unique to this source
);

-- Pivot fast on who, where and when
CREATE INDEX event_user_time ON event (user_name, event_time);
CREATE INDEX event_host_time ON event (host_name, event_time);

  3. Query it the fast way

    Filter on event time and source first, then aggregate to find the accounts that stand out, then drill into their raw events. Note the window is on event_time, so late arrivals still match.

    SQL
    -- Accounts with the most failed sign-ins overnight, worst first
SELECT user_name, count(*) AS failures
FROM event
WHERE source = 'identity'
  AND event_action = 'signin'
  AND outcome = 'failure'
  AND event_time >= '2026-10-18 00:00+00'
  AND event_time <  '2026-10-18 06:00+00'
GROUP BY user_name
ORDER BY failures DESC
LIMIT 20;

Bring it to your team

Ask these the next time someone adds a system or reviews the SIEM bill.

  • What question would this log answer in an investigation? If there is no answer, why are we paying to store it?
  • Which places could an attack happen that we have no log for at all?
  • Is every source in UTC and on a synced clock, and do our rules search by event time?
  • Is each source mapped into the common schema, or does a pivot silently miss it?
  • How long do we keep identity, EDR and cloud audit logs, and would that survive a breach found in six months?
  • Can the people we might investigate delete or edit these logs?

Red flags

  • "Just send everything to the SIEM" with no baseline and no retention plan.
  • Timestamps in local time, or clocks that are never checked.
  • A source whose fields were never mapped, so it is invisible to every pivot.
  • Rules that search by ingest time and quietly miss late events.
  • Logs stored where an admin, or an attacker with admin, can delete them.

Practice

Find sign-ins that no MFA protected

The auth_event table holds identity events in UTC. A sign-in counts as protected only if there is a successful MFA event for the same account in the five minutes up to and including the sign-in. Return every successful sign-in (kind = 'login', outcome = 'success') that was not protected, as account and signed_in_at (formatted YYYY-MM-DD HH24:MI), earliest first. This is the exact gap from the capstone: a working password with no second factor.

SQL exerciseRuns in a real PostgreSQL inside your browser
-- Successful sign-ins not protected by a recent MFA success
SELECT account, to_char(at_utc, 'YYYY-MM-DD HH24:MI') AS signed_in_at
FROM auth_event
WHERE kind = 'login' AND outcome = 'success'
ORDER BY at_utc;
Ctrl + Enter runs
Show a solution
SQL
SELECT l.account, to_char(l.at_utc, 'YYYY-MM-DD HH24:MI') AS signed_in_at
FROM auth_event l
WHERE l.kind = 'login' AND l.outcome = 'success'
  AND NOT EXISTS (
    SELECT 1 FROM auth_event m
    WHERE m.account = l.account
      AND m.kind = 'mfa' AND m.outcome = 'success'
      AND m.at_utc > l.at_utc - interval '5 minutes'
      AND m.at_utc <= l.at_utc
  )
ORDER BY l.at_utc;

The starter returns every successful login, including Ama's, which an MFA success 40 seconds earlier did protect. The NOT EXISTS subquery is the pivot: for each login it looks back five minutes on the same account for a successful MFA. Leila's MFA was ten minutes before, outside the window, and Imran's MFA failed, so both count as unprotected. This is the pattern behind many identity detections.

Pass the exercise to finish this task.

Design the coverage for one question

Mangrove wants to answer: "Did an attacker who took over a store PC copy customer data out to the internet?" Name the log sources and the one or two high-value events in each that you would need, the common-schema field you would pivot on to tie them to one host, and one coverage gap that would make the question unanswerable.

  • You name endpoint logs (process creation) and network logs (DNS and firewall or proxy) as the core sources, and identity for the sign-in
  • You pick a pivot field that ties events to one machine, such as host.name or source.ip
  • You name a real high-value event in each source, not just "the logs"
  • Your coverage gap is concrete, such as the store PCs not sending process logs, or no egress or proxy logging, and you say why it makes the question unanswerable
Show a model answer

Identity: the sign-in to the store PC, to know which account and whether MFA was passed. Endpoint: process creation on that host, to see an archiving or upload tool run and its parent. Network: DNS queries and firewall allow or proxy logs from that host, to see an unfamiliar destination and a large outbound transfer. Pivot on host.name to tie the sign-in, the process and the network events to the one PC, and on user.name to follow the account. The killer coverage gap is the store back-office PCs not shipping process and network logs at all: with no camera on that host, you can see the sign-in but never what ran or what left, so the question cannot be answered whatever rules you write.

Check yourself

A detection rule that looks for sign-ins outside working hours runs every hour on the last hour of data and reads ingest time. A store PC that lost its network sends a 02:00 sign-in only at 09:00. What happens, and what is the fix?

You must answer "which program started this hidden process on the store PC, and with what command line?" Which source answers it?

Two sources name the account differently: the firewall says src_user and the app says actor_email. Neither is mapped to the schema. An analyst pivots on user.name for a suspect account and sees nothing from these two. What is the right read?

Flashcards

Open my review

Key takeaways

  • Choose log sources from the questions an investigation asks; a coverage gap you cannot query is worse than a noisy source you can tune.
  • The high-value events live in identity, endpoint/EDR, network, cloud and application logs; write a baseline every host and app must meet.
  • Keep time trustworthy: UTC, synced clocks, and search by event time so late events are not lost.
  • Map every source into one common schema so a single query can pivot across all of them.
  • Query fast by filtering on time and source first, pivoting on indexed fields, then aggregating and drilling in; keep logs as protected evidence.

Sources and further reading

Episode transcriptThe camera you never pointed · about 10 minutes · tap any line to play from there
  1. NadiaImran, an alert just fired. A sign-in to the back-office PC in Ama's store at two in the morning.
  2. ImranOutside opening hours. So someone is in. What do I even look at first?
  3. NadiaThat is the whole question for today. Not how to respond yet, but which logs let you answer it at all, and how to search them fast.
  4. ImranBecause if the log isn't there, there is nothing to search.
  5. NadiaRight. Think of the cameras in a store. If no camera points at the stockroom door, you will never see who took the missing crates. No matter how hard you look later.
  6. ImranSo coverage comes first. Point cameras where something worth watching happens. The doors, the tills, the stockroom.
  7. NadiaAnd you cannot film every corner at full quality forever. The storage fills, and when something goes wrong you have hours of footage and no way to find the clip.
  8. ImranSo the manager does three more things. One clock on every camera so clips line up. Keep the till footage longer. And jump straight to the time and the camera, instead of scrubbing everything.
  9. NadiaThat is security logging in one picture. Cover what matters, one clock, keep the important streams longer, search by time and source first.
  10. ImranOkay, the first instinct. Just send everything to the SIEM. That is the security information and event management system, the place all the logs land.
  11. NadiaIt feels safe. It is a trap. Every gigabyte costs money, every noisy source buries the events you need, and a query over a year of debug logs times out while you are mid-incident.
  12. ImranSo the question is not what can we collect. It is what do we need to answer.
  13. NadiaExactly. Start from the questions an investigation asks, and each one points at a source and a few high-value events inside it.
  14. ImranWalk me through the sources.
  15. NadiaIdentity, the login system. Sign-ins, failures, second-factor prompts, password resets, privilege changes. It answers who this account is, from where, and did it really prove itself.
  16. NadiaEndpoint, from the tool on each machine. Process creation, with the command line and the parent process. New services and anything that makes a program start on its own. It answers what ran, and what started it.
  17. ImranThen the network.
  18. NadiaFirewall allow and deny, the name lookups a host makes, the web proxy. It answers did this host talk to something it should not. And cloud audit logs answer who changed the environment overnight.
  19. ImranAnd the apps themselves, like the Partner app.
  20. NadiaYes. Sign-in, admin actions, a bank detail or credit limit changed. Things the network can never see because they happen inside the app.
  21. ImranSo from those questions I write a baseline. The short list every host and app must send.
  22. NadiaAnd then you hunt for coverage gaps. The places with no camera. At Mangrove the classic one was the store back-office PCs. Nobody shipped their process logs, so an attacker on one was invisible.
  23. ImranA gap feels worse than noise.
  24. NadiaIt is. A noisy source you can tune. A gap you cannot query at all.
  25. ImranNow here is my problem. The firewall calls the account one thing, the app calls it another, the cloud calls it a third. How do I follow one account across all of them?
  26. NadiaThat is the common schema. If you can see the screen, the first diagram shows five sources flowing into one box that renames every field, then into the SIEM.
  27. NadiaIf not, picture five streams in different languages all translated into one before they are stored. One agreed name for the user, one for the source address, one for the action.
  28. ImranTwo public ones to know are the Elastic Common Schema and the Open Cybersecurity Schema Framework. I do not invent my own. I pick one and map every source into it.
  29. NadiaAnd you do that at collection time, not at search time. The slow work happens once, so every later query is cheap.
  30. ImranThen one search can say, show every event for this account, newest first, and I get sign-ins, processes, firewall hits and app actions in one list.
  31. NadiaThat is a pivot. You follow one account or one host across sources by the same field name, instead of translating in your head.
  32. ImranWhat breaks the pivot?
  33. NadiaA source whose field was never mapped. The search on the user name just skips it, with no error. The account looks quieter than it really is, and you trust an incomplete answer.
  34. ImranSilent. That is the dangerous kind.
  35. NadiaThe most dangerous kind. Now, time. Every investigation is a timeline, so time has to be trustworthy before anything else.
  36. ImranThree rules, you said.
  37. NadiaStore and show everything in one time zone, in coordinated universal time, so cities and servers line up. Keep every clock synced to a time service. And record who saw the event, not just when.
  38. ImranAnd there is the trap about two times.
  39. NadiaYes. Event time is when it happened on the device. Ingest time is when the SIEM saw it. The second diagram lines them up on one axis.
  40. NadiaMost of the time they are seconds apart, so people forget the gap. But a till that lost its network in a storm buffers its logs and sends them hours later.
  41. ImranSo the sign-in happened at two in the morning, but the SIEM only saw it at nine.
  42. NadiaAnd if your rule ran at three and read ingest time, the row was not there yet. The rule missed it.
  43. ImranSo detection searches by event time, and I re-run searches over a window that already passed, to catch the late arrivals.
  44. NadiaThat is it. A row landing seven hours late is not a bug. It is normal, and your queries have to expect it.
  45. ImranWhat about keeping all this? Finance keeps asking about the bill.
  46. NadiaSplit the storage. Hot is fast and holds the recent window you query daily, maybe thirty to ninety days. Cold is cheap and slow and holds the older logs.
  47. ImranAnd someone says, let us keep only fourteen days to save money.
  48. NadiaPush back hard. Breaches are often found months after they start. Fourteen days deletes the evidence of how it began.
  49. ImranEvidence. Because these logs might end up in a dispute.
  50. NadiaOr a court. So keep them where the people you might investigate cannot edit them. Append-only. And watch log access itself, because an attacker who clears logs erases the record you would catch them with.
  51. ImranSo let me say back the build. First the baseline, the list every host and app must send, with a retention tier for each.
  52. NadiaThen one event table in the common schema. Every source maps into the same columns. Event time, ingest time, source, host name, user name, source address, action, outcome.
  53. ImranAnd I index the fields I pivot on. The user name, the host name, and the event time.
  54. NadiaThen query the fast way. Filter on event time and source first, to shrink billions of rows to thousands. Then aggregate to find the few that stand out. Then drill into their raw events.
  55. ImranNot pull raw rows and read them by eye.
  56. NadiaThat is how an investigation runs out of time.
  57. NadiaBack to our alert. One console, one schema. I pivot on the store manager's account and read one timeline. Sign-in success, a new address, no second factor before it.
  58. ImranThen?
  59. NadiaA moment later, an office document starts a command shell on that PC. Then a lookup for an unfamiliar domain, and an allowed outbound connection. Three sources, one pivot, under five minutes.
  60. ImranAnd the two in the morning sign-in was one of the late ones.
  61. NadiaIt was. The store link had dropped and the PC sent it at nine. Our overnight rule ran on event time, so it still caught it.
  62. ImranQuestions for the next review, then. What question would this log answer in an investigation. If there is no answer, why are we paying to store it.
  63. NadiaAnd, which places could an attack happen that we have no log for at all. Plus, can the people we might investigate delete these logs.
  64. NadiaHere is one to try. A sign-in succeeds. Is the account safe if there was a second-factor success ten minutes earlier. Take a second.
  65. ImranNo. The lesson counts a sign-in as protected only if the second factor passed in the five minutes right before it. Ten minutes earlier is outside the window.
  66. NadiaExactly. The practice query finds every successful sign-in that no recent second factor protected. The exact gap that let someone into that store PC.
  67. ImranMy summary. One, choose sources from the questions you need to answer, and a gap you cannot query is worse than noise you can tune.
  68. ImranTwo, one schema and one clock, so a single query pivots across every source and the timeline lines up.
  69. NadiaAnd three, search by event time so late rows are not lost, and query fast by filtering time and source before anything else.
  70. ImranWhich gets me a clean timeline. But not a rule that fires by itself.
  71. NadiaNext time. How to turn these pivots into detections you can share and test, in a portable rule format.
Finished reading?Mark it complete to fill in its stop on your course line.

Use it at work

Log triage

Parse auth log lines, group failures by account and address, and flag brute force and spraying.

Open in the Security Lab
Next stopWriting detections
Was this lesson helpful?
Questions and discussion

Loading the discussion…