ACSC Logo
© 2026 GEMXIT
GEMXIT Logo
Agent Foskett Academy • Lesson 15 • Building Investigation Timelines with KQL

Building Investigation Timelines with KQL

Security events rarely happen in isolation.
An email arrives.
A link is clicked.
A sign-in follows.
A process starts on a device.

The truth is often hidden in the order of events.

In this Agent Foskett Academy lesson, you will learn how defenders use timestamps, project, sort, union and clear event labels to build readable investigation timelines inside Microsoft Defender XDR and Microsoft Sentinel.

Start Lesson 15 Back to Academy
Agent Foskett Academy lesson explaining how to build investigation timelines with KQL
Lesson overview

Learn how to turn Microsoft Defender XDR telemetry into a clear timeline that explains what happened, when it happened and why it matters.

Use timestamps as evidence
Label events clearly
Build readable incident timelines
🕒 Timelines turn scattered logs into a story.
A good timeline helps defenders explain the sequence: delivery, click, sign-in, endpoint activity and response.
Review Lesson 14 →

Why timelines matter

During an investigation, the question is not only whether something happened.

The question is often: what happened first, what happened next and what changed after that?

A timeline helps defenders explain the story in a way that analysts, managers and responders can understand. It also helps separate coincidence from cause and effect.
Show the sequencePut events in the correct order so the investigation has a clear beginning, middle and end.
Explain impactUse the timeline to show whether a click was followed by sign-in, endpoint or network activity.
Support decisionsClear timelines help responders decide whether to reset passwords, isolate devices or escalate the incident.

Investigation scenario

A suspicious email was delivered to a user.

The user clicked the link. Minutes later, there was a suspicious sign-in and then endpoint activity from the same account.

The analyst needs to build a simple timeline so the incident can be explained clearly.

Step 1 — Start with one table and sort by time

Every timeline starts with timestamps. Begin by reviewing one table in chronological order.
email-timeline-start.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
EmailEvents
| where Timestamp > ago(7d)
| where RecipientEmailAddress == "user@contoso.com"
| project Timestamp, RecipientEmailAddress, SenderFromAddress, Subject, DeliveryAction
| sort by Timestamp asc

Step 2 — Add an event label

When building timelines, add a clear EventType field so the result is easy to read.
label-email-events.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
EmailEvents
| where Timestamp > ago(7d)
| where RecipientEmailAddress == "user@contoso.com"
| extend EventType = "Email delivered"
| project TimelineTime = Timestamp, EventType, Account = RecipientEmailAddress, Detail = Subject, Source = SenderFromAddress
| sort by TimelineTime asc

Step 3 — Build a timeline from multiple tables

Use union when you want to stack events from different tables into one timeline view.
combined-investigation-timeline.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
let targetUser = "user@contoso.com";
let emailEvents =
EmailEvents
| where Timestamp > ago(7d)
| where RecipientEmailAddress == targetUser
| project TimelineTime = Timestamp, EventType = "Email delivered", Account = RecipientEmailAddress, Detail = Subject, Source = SenderFromAddress;
let urlClicks =
UrlClickEvents
| where Timestamp > ago(7d)
| where AccountUpn == targetUser
| project TimelineTime = Timestamp, EventType = "URL clicked", Account = AccountUpn, Detail = Url, Source = ActionType;
union emailEvents, urlClicks
| sort by TimelineTime asc

What union does

The union operator stacks rows from multiple result sets together.

For timeline work, this is useful because each table can be shaped into the same column structure first. Once the columns match, the events can be displayed together in time order.
Same column namesProject each table into shared timeline columns such as TimelineTime, EventType, Account, Detail and Source.
Clear event labelsUse EventType values like Email delivered, URL clicked, Sign-in and Process started.
Sort ascendingUse ascending time order when you want to explain the sequence of activity.

Step 4 — Add sign-in activity

If the investigation includes identity behaviour, add sign-in events to the same timeline structure.
add-signins-to-timeline.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName == "user@contoso.com"
| project TimelineTime = TimeGenerated,
          EventType = "User sign-in",
          Account = UserPrincipalName,
          Detail = ResultDescription,
          Source = IPAddress

Step 5 — Build a fuller incident timeline

Now combine email delivery, URL clicks and sign-ins into one readable view.
full-incident-timeline.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
  18. 18
let targetUser = "user@contoso.com";
let emailEvents = EmailEvents
| where Timestamp > ago(7d)
| where RecipientEmailAddress == targetUser
| project TimelineTime = Timestamp, EventType = "Email delivered", Account = RecipientEmailAddress, Detail = Subject, Source = SenderFromAddress;
let urlClicks = UrlClickEvents
| where Timestamp > ago(7d)
| where AccountUpn == targetUser
| project TimelineTime = Timestamp, EventType = "URL clicked", Account = AccountUpn, Detail = Url, Source = ActionType;
let signIns = SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName == targetUser
| project TimelineTime = TimeGenerated, EventType = "User sign-in", Account = UserPrincipalName, Detail = ResultDescription, Source = IPAddress;
union emailEvents, urlClicks, signIns
| sort by TimelineTime asc

Investigator notes

A timeline should be readable, not just technically correct.

If another analyst cannot quickly understand the sequence, add clearer labels, remove unnecessary columns and keep the output focused on the investigation question.
Use plain labelsEventType should explain the event without forcing someone to know the table name.
Keep useful detailsThe Detail column should contain the subject, URL, sign-in result or process command that matters.
Know your time fieldsDifferent tables may use Timestamp or TimeGenerated. Normalize them into TimelineTime.
🎓 Agent Foskett Academy — Tell the story in order
You now understand how to shape different Microsoft security tables into one clear investigation timeline.
Return to Academy

What you learned

In this lesson, you learned how to build investigation timelines with KQL.
Using timestampsTimestamps are the backbone of every investigation timeline.
Using unionUnion can combine shaped results from different tables into one timeline view.
Explaining the incidentA clear timeline helps defenders explain delivery, clicks, sign-ins and follow-on activity in order.

Continue your investigation

The next step is learning how to use let statements to make longer KQL investigations cleaner, easier to read and easier to reuse.
Agent Foskett Academy Return to the full Academy learning path and review earlier KQL foundation lessons.
The Link Was Clicked After The Email Was Delivered Continue into a real-world investigation where timing helps explain what happened after delivery.

Continue learning with Connecting Tables with join, UrlClickEvents, EmailEvents KQL Guide, KQL Threat Hunting Guide and Microsoft Security.

Develop IT. Protect IT. GEMXIT PTY LTD | GEMXIT UK LTD
ACSC Logo

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT 2026

Building Investigation Timelines with KQL

Agent Foskett Academy Lesson 15 teaches defenders how to build investigation timelines with KQL using timestamps, event labels, union, project and Microsoft Defender XDR telemetry.

Learn KQL timelines for Microsoft Defender XDR

KQL timelines help analysts explain the sequence of email delivery, URL clicks, sign-ins and follow-on activity across Microsoft security tables.

KQL investigation timeline lesson for Microsoft security analysts

This Agent Foskett Academy lesson explains how to normalize different time fields, label events clearly and build readable incident timelines inside Microsoft Defender XDR and Microsoft Sentinel.