Investigate Account Takeover with KQL

Investigate a synthetic CEO takeover with KQL and uncover BEC staging.

Introduction

30 Second Summary

A foreign sign-in to a company leader's account can signal an attacker or ordinary travel. The surrounding evidence decides which story is true.

In this project, you will investigate a possible account takeover affecting a fictional CEO with Kusto Query Language (KQL) in browser-based Azure Data Explorer. You'll turn synthetic identity evidence into a defensible incident verdict with a clear containment plan.

What You'll Build

You present a live investigation where KQL turns one ambiguous CEO alert into a defensible verdict covering two compromised accounts.

By the end of this project, you'll have:

  • A zero-billing evidence lab where you run KQL across a complete synthetic attack timeline. You can explain how the query patterns transfer into Microsoft Sentinel.
  • An evidence-backed verdict that clears Maria Chen's legitimate travel. It confirms Charlotte Reed's takeover through MFA fatigue. It reveals identity persistence. It uncovers business email compromise staging.
  • A scoped incident report naming Daniel Wu as a second affected account. It maps the attack to MITRE ATT&CK. It prioritizes containment actions.
  • Secret Mission: Build a time-bounded KQL detection that correlates suspicious successful sign-ins with MFA persistence events occurring within 15 minutes.

Are there any prerequisites?

No. The project guides you through every tool from setup using synthetic evidence.

Before We Start

Before the investigation begins, lock in the question you need to answer. You will assess a synthetic CEO alert without treating a foreign sign-in as proof or creating any billing risk.

Set Up the Zero-Billing Investigation Lab

A live Microsoft Sentinel workspace can introduce billing risk before you investigate a single event. Your first job is to create a safe place for synthetic evidence.

Azure Data Explorer provides a free browser-based cluster that runs Kusto Query Language (KQL). In this step, you'll prepare a zero-billing investigation lab.

In this step, get ready to:
  • Open the required Windows apps.
  • Create the free Cloudora cluster.
  • Run a readiness query in the Cloudora database.
Prepare the Windows apps

On Windows, Microsoft Edge gives you access to the browser-based investigation lab. Notepad gives you a simple editor for the evidence scripts used later.

  • Press the Windows key to open Start.
  • Type Microsoft Edge into the search box.
  • Select Microsoft Edge from the search results.
  • Press the Windows key again.
  • Type Notepad into the search box.
  • Select Notepad from the search results.

✔️ Both apps are open

Both apps are open. Your local tools are ready for the investigation lab.

ⓧ One or both apps are missing

A missing app needs its Microsoft-published installer before the lab setup continues.

Install Microsoft Edge if needed
  • Visit the official Microsoft Edge download page in any available browser.
  • Select Download Edge for Windows.
  • Run the downloaded installer.
  • Approve the Windows permission prompt by selecting Yes.

The installation is complete when Microsoft Edge opens successfully.

  • Press the Windows key.
  • Type Microsoft Edge into the search box.
  • Select Microsoft Edge from the search results.
Install Notepad if needed
  • Press the Windows key.
  • Type Microsoft Store into the search box.
  • Select Microsoft Store from the search results.
  • Search for Notepad.
  • Select the Microsoft-published Notepad result.
  • Select Install.

Notepad is installed when Microsoft Store finishes the download.

  • Press the Windows key.
  • Type Notepad into the search box.
  • Select Notepad from the search results.

Still Unable to Open an App?

  • Check that the installer publisher is Microsoft.
  • Ask your device administrator for help if Windows blocks the installation.

Help me install Microsoft Edge or Notepad on my Windows device.

Create the free Cloudora cluster

A free cluster gives you a real KQL environment for the synthetic Cloudora evidence. The cluster holds the database that every investigation query uses.

Why Azure Data Explorer?

Azure Data Explorer runs KQL against tables in a browser. Microsoft Sentinel also uses KQL for security analysis.

The free cluster accepts this project's synthetic evidence without an Azure subscription. It requires no credit card.

  • Open Microsoft's free Azure Data Explorer cluster guide in Microsoft Edge.
  • Follow the guide's free-cluster link to enter the browser experience.
  • Create a Microsoft account from the sign-in page if you do not already have one.
  • Sign in with your Microsoft account.
  • Select My Cluster.
  • Select Create cluster.
  • Enter CloudoraSOC in the cluster display name field.
  • Enter CloudoraLab in the database name field.

Stay on the Free Path

This setup remains at $0 only while you use the free cluster. The free path asks for no Azure subscription or credit card.

  • Choose the nearest available geography in the location field.
  • Accept the free-cluster terms.
  • Leave Upgrade unselected.
  • Select Create.
  • Wait for the cluster overview to load.
  • Confirm the overview displays CloudoraSOC.
  • Confirm the database area lists CloudoraLab.

That's the billing hurdle cleared. Your free cluster is ready with CloudoraLab inside it.

Cluster Creation Not Working?

  • Return to the free-cluster guide if the page requests an Azure subscription.
  • Check that every required creation field contains a value.
  • Choose another available geography if your first location cannot create the cluster.

Help me troubleshoot my Azure Data Explorer free-cluster setup.

Verify the KQL query editor

The query editor is where you investigate Cloudora's synthetic evidence. A simple readiness query confirms that the editor can execute KQL in the correct database.

  • Select Query data from the cluster overview.
  • Select CloudoraLab as the query context.
  • Load the readiness check into the editor by pasting this query:
print Status="Cloudora SOC lab ready"

What Does This Query Do?

The print operator creates a one-row table from a scalar expression. Status names the output column.

The value Cloudora SOC lab ready gives you a clear success signal in the results grid.

Before you run it, make a quick prediction about the number of result rows.

  • Execute the query with Run or Shift + Enter.

You should see one row in the results grid with Cloudora SOC lab ready under Status. Your Cloudora KQL lab can now accept investigation queries.

Readiness Row Missing?

  • Confirm that CloudoraLab is selected as the database context.
  • Replace the editor contents with the exact readiness query above if your result differs.
  • Run the query again with Run or Shift + Enter.

Help me fix my Cloudora lab readiness query.

Your zero-billing investigation lab is ready. Next, you'll load the synthetic evidence for the first CEO alert triage.

Load and Triage the CEO Alert

Your CloudoraLab query editor is ready. The CEO alert still needs evidence before you can judge it.

In this step, you will load synthetic records into Azure Data Explorer. Your first Kusto Query Language (KQL) summary will reveal a suspicious location change without proving an account takeover.

In this step, get ready to:
  • Build a local evidence pack containing sign-in, audit, and mailbox records.
  • Load three synthetic evidence tables into CloudoraLab.
  • Triage the CEO alert without declaring a premature verdict.
Create the local evidence pack

The evidence pack keeps the investigation repeatable. Every identity, address, timestamp, and business action in these files is synthetic.

Keep the lab synthetic

The free cluster should only contain the supplied Cloudora evidence. Do not replace it with personal, regulated, or production data.

  • Return to Notepad from earlier.
  • Create a blank document in Notepad.
  • Build the sign-in evidence script by copying this code into the blank document:
.set-or-replace CloudoraSigninLogs <|
datatable(
    TimeGenerated:datetime,
    UserPrincipalName:string,
    IPAddress:string,
    Country:string,
    DeviceId:string,
    OperatingSystem:string,
    Browser:string,
    Result:string,
    AuthenticationDetails:string,
    SessionId:string
)[
    datetime(2026-08-18T07:45:00), "charlotte.reed@cloudora.example", "198.51.100.10", "United Kingdom", "CEO-LT-01", "Windows 11", "Edge", "Success", "MFA satisfied", "LEGIT-CEO-001",
    datetime(2026-08-19T07:50:00), "charlotte.reed@cloudora.example", "198.51.100.11", "United Kingdom", "CEO-LT-01", "Windows 11", "Edge", "Success", "MFA satisfied", "LEGIT-CEO-002",
    datetime(2026-08-18T08:15:00), "maria.chen@cloudora.example", "198.51.100.21", "United Kingdom", "SALES-LT-04", "Windows 11", "Edge", "Success", "MFA satisfied", "LEGIT-MARIA-001",
    datetime(2026-08-20T07:55:00), "maria.chen@cloudora.example", "192.0.2.44", "Spain", "SALES-LT-04", "Windows 11", "Edge", "Success", "MFA satisfied", "TRAVEL-MARIA-002",
    datetime(2026-08-20T09:00:00), "charlotte.reed@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Failure", "MFA denied", "ATTACK-CEO-900",
    datetime(2026-08-20T09:02:00), "charlotte.reed@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Failure", "MFA denied", "ATTACK-CEO-900",
    datetime(2026-08-20T09:04:00), "charlotte.reed@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Success", "MFA approved", "ATTACK-CEO-900",
    datetime(2026-08-20T09:08:00), "charlotte.reed@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Success", "Session active", "ATTACK-CEO-900",
    datetime(2026-08-19T08:30:00), "daniel.wu@cloudora.example", "198.51.100.30", "United Kingdom", "FIN-LT-02", "Windows 11", "Edge", "Success", "MFA satisfied", "LEGIT-DANIEL-001",
    datetime(2026-08-20T09:29:00), "daniel.wu@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Failure", "MFA denied", "ATTACK-FIN-901",
    datetime(2026-08-20T09:31:00), "daniel.wu@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Success", "MFA approved", "ATTACK-FIN-901"
]

What does this script contain?

  • The datatable expression defines the schema for authentication evidence.
  • Each row captures an identity, source address, country, device, authentication result, and session.
  • The .set-or-replace command loads those rows into CloudoraSigninLogs.
  • Open the Save As dialog by pressing Ctrl+S.
  • Select Documents in the left pane.
  • Create a new folder named CloudoraInvestigation.
  • Open the CloudoraInvestigation folder.
  • Enter cloudora-signins.kql in the File name box.
  • Select All Files (*.*) from Save as type.
  • Select Save.
  • Confirm the Notepad title bar shows cloudora-signins.kql.

Does the file end in .txt?

Return to the Save As dialog. Select All Files (*.*) before saving the exact cloudora-signins.kql filename again.

Use this if the extension still changes: Help me save cloudora-signins.kql in Notepad without adding .txt.

✔️ The sign-in script is saved

Great. Your local evidence pack now has its authentication data source.

ⓧ I'd like to double check the full code

Compare your saved cloudora-signins.kql file with this reference.

.set-or-replace CloudoraSigninLogs <|
datatable(
    TimeGenerated:datetime,
    UserPrincipalName:string,
    IPAddress:string,
    Country:string,
    DeviceId:string,
    OperatingSystem:string,
    Browser:string,
    Result:string,
    AuthenticationDetails:string,
    SessionId:string
)[
    datetime(2026-08-18T07:45:00), "charlotte.reed@cloudora.example", "198.51.100.10", "United Kingdom", "CEO-LT-01", "Windows 11", "Edge", "Success", "MFA satisfied", "LEGIT-CEO-001",
    datetime(2026-08-19T07:50:00), "charlotte.reed@cloudora.example", "198.51.100.11", "United Kingdom", "CEO-LT-01", "Windows 11", "Edge", "Success", "MFA satisfied", "LEGIT-CEO-002",
    datetime(2026-08-18T08:15:00), "maria.chen@cloudora.example", "198.51.100.21", "United Kingdom", "SALES-LT-04", "Windows 11", "Edge", "Success", "MFA satisfied", "LEGIT-MARIA-001",
    datetime(2026-08-20T07:55:00), "maria.chen@cloudora.example", "192.0.2.44", "Spain", "SALES-LT-04", "Windows 11", "Edge", "Success", "MFA satisfied", "TRAVEL-MARIA-002",
    datetime(2026-08-20T09:00:00), "charlotte.reed@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Failure", "MFA denied", "ATTACK-CEO-900",
    datetime(2026-08-20T09:02:00), "charlotte.reed@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Failure", "MFA denied", "ATTACK-CEO-900",
    datetime(2026-08-20T09:04:00), "charlotte.reed@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Success", "MFA approved", "ATTACK-CEO-900",
    datetime(2026-08-20T09:08:00), "charlotte.reed@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Success", "Session active", "ATTACK-CEO-900",
    datetime(2026-08-19T08:30:00), "daniel.wu@cloudora.example", "198.51.100.30", "United Kingdom", "FIN-LT-02", "Windows 11", "Edge", "Success", "MFA satisfied", "LEGIT-DANIEL-001",
    datetime(2026-08-20T09:29:00), "daniel.wu@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Failure", "MFA denied", "ATTACK-FIN-901",
    datetime(2026-08-20T09:31:00), "daniel.wu@cloudora.example", "203.0.113.77", "Nigeria", "Unregistered", "Linux", "Firefox", "Success", "MFA approved", "ATTACK-FIN-901"
]

What should match?

Your file should define CloudoraSigninLogs with all eleven synthetic sign-in rows shown here.

  • Create cloudora-audit.kql by copying this script into a new blank Notepad document:
.set-or-replace CloudoraAuditLogs <|
datatable(
    TimeGenerated:datetime,
    InitiatedBy:string,
    OperationName:string,
    TargetUser:string,
    IPAddress:string,
    Details:string,
    CorrelationId:string
)[
    datetime(2026-08-20T09:07:00), "charlotte.reed@cloudora.example", "MFA method registered", "charlotte.reed@cloudora.example", "203.0.113.77", "Alternate authentication method added", "ATTACK-CEO-900",
    datetime(2026-08-20T09:09:00), "charlotte.reed@cloudora.example", "OAuth application consent granted", "charlotte.reed@cloudora.example", "203.0.113.77", "Mail access permission granted", "ATTACK-CEO-900",
    datetime(2026-08-20T09:33:00), "daniel.wu@cloudora.example", "MFA method registered", "daniel.wu@cloudora.example", "203.0.113.77", "Alternate authentication method added", "ATTACK-FIN-901"
]

What does this script contain?

  • The schema captures account changes with their initiator, target identity, source address, and correlation value.
  • The rows record synthetic MFA registration activity plus an application-consent event.
  • The ingestion command loads the records into CloudoraAuditLogs.
  • Open the Save As dialog by pressing Ctrl+S.
  • Open Documents\CloudoraInvestigation in the Save As dialog.
  • Enter cloudora-audit.kql in the File name box.
  • Select All Files (*.*) from Save as type.
  • Select Save.
  • Confirm the Notepad title bar shows cloudora-audit.kql.

Is the audit script incomplete?

Check that the first line begins with .set-or-replace CloudoraAuditLogs. Confirm the final line contains the closing square bracket.

Use this for a line-by-line comparison: Help me compare my cloudora-audit.kql file with the supplied script.

✔️ The audit script is saved

Your evidence pack now includes the account-change trail.

ⓧ I'd like to double check the full code

Compare your saved cloudora-audit.kql file with this reference.

.set-or-replace CloudoraAuditLogs <|
datatable(
    TimeGenerated:datetime,
    InitiatedBy:string,
    OperationName:string,
    TargetUser:string,
    IPAddress:string,
    Details:string,
    CorrelationId:string
)[
    datetime(2026-08-20T09:07:00), "charlotte.reed@cloudora.example", "MFA method registered", "charlotte.reed@cloudora.example", "203.0.113.77", "Alternate authentication method added", "ATTACK-CEO-900",
    datetime(2026-08-20T09:09:00), "charlotte.reed@cloudora.example", "OAuth application consent granted", "charlotte.reed@cloudora.example", "203.0.113.77", "Mail access permission granted", "ATTACK-CEO-900",
    datetime(2026-08-20T09:33:00), "daniel.wu@cloudora.example", "MFA method registered", "daniel.wu@cloudora.example", "203.0.113.77", "Alternate authentication method added", "ATTACK-FIN-901"
]

What should match?

Your file should define CloudoraAuditLogs with the three synthetic audit rows shown here.

  • Create cloudora-mailbox.kql by copying this script into a new blank Notepad document:
.set-or-replace CloudoraMailboxLogs <|
datatable(
    TimeGenerated:datetime,
    UserPrincipalName:string,
    Operation:string,
    IPAddress:string,
    Recipient:string,
    Subject:string,
    Details:string,
    SessionId:string
)[
    datetime(2026-08-20T09:12:00), "charlotte.reed@cloudora.example", "Mailbox opened", "203.0.113.77", "", "", "Executive mailbox accessed", "ATTACK-CEO-900",
    datetime(2026-08-20T09:15:00), "charlotte.reed@cloudora.example", "Inbox rule created", "203.0.113.77", "external-payments@example.net", "", "Finance-related messages forwarded externally", "ATTACK-CEO-900",
    datetime(2026-08-20T09:18:00), "charlotte.reed@cloudora.example", "Draft created", "203.0.113.77", "finance@cloudora.example", "Updated supplier bank details", "Urgent payment request saved as a draft", "ATTACK-CEO-900",
    datetime(2026-08-20T09:21:00), "charlotte.reed@cloudora.example", "Message deleted", "203.0.113.77", "security@cloudora.example", "Unusual sign-in alert", "Security notification moved to deleted items", "ATTACK-CEO-900",
    datetime(2026-08-20T09:35:00), "daniel.wu@cloudora.example", "Mailbox opened", "203.0.113.77", "", "", "Finance mailbox accessed", "ATTACK-FIN-901",
    datetime(2026-08-20T09:38:00), "daniel.wu@cloudora.example", "Invoice search", "203.0.113.77", "", "Q3 supplier invoices", "Invoice and payment schedule messages searched", "ATTACK-FIN-901"
]

What does this script contain?

  • The schema describes mailbox operations with their user, source address, recipient, subject, details, and session.
  • The rows capture synthetic mailbox access plus actions involving forwarding, drafts, deletion, and invoice searches.
  • The ingestion command loads the records into CloudoraMailboxLogs.
  • Open the Save As dialog by pressing Ctrl+S.
  • Open Documents\CloudoraInvestigation in the Save As dialog.
  • Enter cloudora-mailbox.kql in the File name box.
  • Select All Files (*.*) from Save as type.
  • Select Save.
  • Confirm the Notepad title bar shows cloudora-mailbox.kql.

Is a mailbox row malformed?

Keep every empty string as two quotation marks. Check that each row ends with a session value before its comma.

Use this to locate the mismatch: Help me find a formatting problem in cloudora-mailbox.kql.

✔️ The mailbox script is saved

Your local evidence pack now covers authentication, account changes, and mailbox activity.

ⓧ I'd like to double check the full code

Compare your saved cloudora-mailbox.kql file with this reference.

.set-or-replace CloudoraMailboxLogs <|
datatable(
    TimeGenerated:datetime,
    UserPrincipalName:string,
    Operation:string,
    IPAddress:string,
    Recipient:string,
    Subject:string,
    Details:string,
    SessionId:string
)[
    datetime(2026-08-20T09:12:00), "charlotte.reed@cloudora.example", "Mailbox opened", "203.0.113.77", "", "", "Executive mailbox accessed", "ATTACK-CEO-900",
    datetime(2026-08-20T09:15:00), "charlotte.reed@cloudora.example", "Inbox rule created", "203.0.113.77", "external-payments@example.net", "", "Finance-related messages forwarded externally", "ATTACK-CEO-900",
    datetime(2026-08-20T09:18:00), "charlotte.reed@cloudora.example", "Draft created", "203.0.113.77", "finance@cloudora.example", "Updated supplier bank details", "Urgent payment request saved as a draft", "ATTACK-CEO-900",
    datetime(2026-08-20T09:21:00), "charlotte.reed@cloudora.example", "Message deleted", "203.0.113.77", "security@cloudora.example", "Unusual sign-in alert", "Security notification moved to deleted items", "ATTACK-CEO-900",
    datetime(2026-08-20T09:35:00), "daniel.wu@cloudora.example", "Mailbox opened", "203.0.113.77", "", "", "Finance mailbox accessed", "ATTACK-FIN-901",
    datetime(2026-08-20T09:38:00), "daniel.wu@cloudora.example", "Invoice search", "203.0.113.77", "", "Q3 supplier invoices", "Invoice and payment schedule messages searched", "ATTACK-FIN-901"
]

What should match?

Your file should define CloudoraMailboxLogs with the six synthetic mailbox rows shown here.

Load the evidence tables

Each ingestion script creates its table when the table does not exist. Running the same script again replaces that table's data with the supplied rows.

  • Return to cloudora-signins.kql in Notepad.
  • Copy the full contents of cloudora-signins.kql.
  • Switch back to the CloudoraLab query editor in Microsoft Edge.
  • Replace the current editor contents with the copied sign-in script.
  • Select Run.

The command should complete without an error. Your first evidence source is now loaded into CloudoraLab.

Did the sign-in script fail?

Confirm that CloudoraLab is still selected as the query context. Compare the first line plus the closing parentheses and square bracket with the saved file.

Use this to inspect the failure: Help me troubleshoot why cloudora-signins.kql failed in Azure Data Explorer.

  • Return to cloudora-audit.kql in Notepad.
  • Copy the full contents of cloudora-audit.kql.
  • Switch back to the CloudoraLab query editor.
  • Replace the current editor contents with the copied audit script.
  • Select Run.

The command should complete without an error. The database now holds the synthetic account-change evidence.

  • Return to cloudora-mailbox.kql in Notepad.
  • Copy the full contents of cloudora-mailbox.kql.
  • Switch back to the CloudoraLab query editor.
  • Replace the current editor contents with the copied mailbox script.
  • Select Run.

The command should complete without an error. All three investigation data sources are now loaded.

Before you check the database, predict which three table names should appear in the inventory.

  • Check the table inventory by running this command in the CloudoraLab query editor:
.show tables

What does this command do?

The .show tables management command lists the tables in the selected database. It confirms that each ingestion script created its intended destination.

You should see CloudoraSigninLogs, CloudoraAuditLogs, and CloudoraMailboxLogs in the results grid.

Is a table missing?

Return to the matching saved script. Run that script again with CloudoraLab selected before repeating the table check.

Use this if the table still does not appear: Help me identify why one Cloudora table is missing after running its ingestion script.

Triage the CEO sign-in window

A triage query condenses the CEO's sign-in history into a few investigation fields. This gives you a fast anomaly assessment before you examine individual events.

  • Create triage.kql by copying this query into a new blank Notepad document:
let AlertedUser = "charlotte.reed@cloudora.example";
CloudoraSigninLogs
| where UserPrincipalName == AlertedUser
| summarize
    FirstSeen=min(TimeGenerated),
    LastSeen=max(TimeGenerated),
    Countries=make_set(Country),
    Devices=make_set(DeviceId),
    Attempts=count()

What does this query do?

  • The AlertedUser value keeps the query focused on Charlotte Reed.
  • The where operator keeps only rows belonging to that identity.
  • The summarize operator calculates the first event, last event, country set, device set, and attempt count.
  • Open the Save As dialog by pressing Ctrl+S.
  • Open Documents\CloudoraInvestigation in the Save As dialog.
  • Enter triage.kql in the File name box.
  • Select All Files (*.*) from Save as type.
  • Select Save.
  • Confirm the Notepad title bar shows triage.kql.

Does the triage query look incomplete?

Confirm the let statement ends with a semicolon inside the file. Keep every aggregation inside the summarize section.

Use this to compare the structure: Help me check my triage.kql query for a missing line or character.

✔️ The triage query is saved

Your investigation folder now contains all four files required for this step.

ⓧ I'd like to double check the full code

Compare your saved triage.kql file with this reference.

let AlertedUser = "charlotte.reed@cloudora.example";
CloudoraSigninLogs
| where UserPrincipalName == AlertedUser
| summarize
    FirstSeen=min(TimeGenerated),
    LastSeen=max(TimeGenerated),
    Countries=make_set(Country),
    Devices=make_set(DeviceId),
    Attempts=count()

What should match?

Your file should define AlertedUser as charlotte.reed@cloudora.example and return the five summary fields shown here.

Before you run the query, ask yourself whether a location and device summary can support a final compromise verdict.

  • Copy the full contents of triage.kql from Notepad.
  • Switch back to the CloudoraLab query editor.
  • Replace the current editor contents with the copied triage query.
  • Run the query by selecting Run or pressing Shift + Enter.

You should see one summary row with two countries and two device identities. The countries are United Kingdom and Nigeria.

The device identities are CEO-LT-01 and Unregistered. The summary shows six sign-in attempts across the investigation window.

What has triage proved?

The CEO account appears across two countries plus two device identities. That combination makes the alert suspicious.

The query does not establish whether the foreign activity belongs to an attacker or a legitimate traveler. Your current verdict remains unconfirmed.

  • Record your current classification in the checkpoint below.

Strong triage work. You have identified a real anomaly without turning suspicion into an unsupported conclusion.

Next, you will compare the CEO with a known traveler and test which identity trail contains evidence of compromise.

Separate Legitimate Travel from Account Takeover

Your triage query narrowed the CEO alert to two countries and two devices. Geography still leaves legitimate travel and an account takeover in play.

This step compares Charlotte Reed with a known traveler in Azure Data Explorer. A shared Kusto Query Language (KQL) timeline produces the evidence needed for a defensible verdict.

In this step, get ready to:
  • Build a combined sign-in and audit timeline for Charlotte Reed and Maria Chen.
  • Classify Maria Chen's Spain sign-in as a false positive.
  • Confirm Charlotte Reed's account takeover using authentication and persistence evidence.
Build the comparison timeline

A comparison timeline needs each evidence source to use the same output columns. The union operator can then place sign-in rows beside audit rows in time order.

  • Return to the CloudoraLab query editor from earlier.
  • Replace the current triage query with the comparison query below:
union
(
    CloudoraSigninLogs
    | where UserPrincipalName == "charlotte.reed@cloudora.example"
        or UserPrincipalName == "maria.chen@cloudora.example"
    | project
        TimeGenerated,
        User=UserPrincipalName,
        Source="Sign-in",
        Action=strcat(Result, " / ", AuthenticationDetails),
        IPAddress,
        Evidence=strcat(Country, " | ", DeviceId, " | ", OperatingSystem, " | ", Browser)
),
(
    CloudoraAuditLogs
    | where TargetUser == "charlotte.reed@cloudora.example"
        or TargetUser == "maria.chen@cloudora.example"
    | project
        TimeGenerated,
        User=TargetUser,
        Source="Audit",
        Action=OperationName,
        IPAddress,
        Evidence=Details
)
| sort by TimeGenerated asc

What does this query do?

  • The first branch filters CloudoraSigninLogs to Charlotte Reed and Maria Chen.
  • The project operator reshapes sign-in evidence into the shared timeline columns.
  • The second branch adds matching rows from CloudoraAuditLogs.
  • The final sort by TimeGenerated asc line reconstructs the evidence chronologically.

Before you run the timeline, which account do you expect to look more suspicious once device continuity and audit activity are included?

  • Run the query by selecting Run or pressing Shift + Enter.

You'll see one time-ordered results grid containing both users. The Source column separates Sign-in evidence from Audit evidence.

Timeline missing rows?

  • Confirm CloudoraLab remains selected as the query context.
  • Check that the first branch reads from CloudoraSigninLogs.
  • Check that the second branch reads from CloudoraAuditLogs.

Help me troubleshoot missing rows in my comparison timeline.

The comparison query now runs against the synthetic evidence. Saving this validated version gives you a reusable investigation file.

  • Copy the full working query from Azure Data Explorer.
  • Switch back to Notepad.
  • Paste the copied query into a blank Notepad document.
  • Save the document as confirm-compromise.kql inside Documents\CloudoraInvestigation by using the Save As control in the File menu.

You should see confirm-compromise.kql as the saved document name in Notepad.

✔️ Awesome, I've got everything!

Your comparison query runs successfully. The validated confirm-compromise.kql file is saved inside Documents\CloudoraInvestigation.

ⓧ I'd like to double check the full code

Compare your saved confirm-compromise.kql file with the complete query below.

union
(
    CloudoraSigninLogs
    | where UserPrincipalName == "charlotte.reed@cloudora.example"
        or UserPrincipalName == "maria.chen@cloudora.example"
    | project
        TimeGenerated,
        User=UserPrincipalName,
        Source="Sign-in",
        Action=strcat(Result, " / ", AuthenticationDetails),
        IPAddress,
        Evidence=strcat(Country, " | ", DeviceId, " | ", OperatingSystem, " | ", Browser)
),
(
    CloudoraAuditLogs
    | where TargetUser == "charlotte.reed@cloudora.example"
        or TargetUser == "maria.chen@cloudora.example"
    | project
        TimeGenerated,
        User=TargetUser,
        Source="Audit",
        Action=OperationName,
        IPAddress,
        Evidence=Details
)
| sort by TimeGenerated asc
Classify Maria Chen's travel

Location is a weak signal on its own. Device continuity plus normal multi-factor authentication (MFA) behavior tests the travel explanation.

  • Return to the results grid in Microsoft Edge.
  • Find the rows for maria.chen@cloudora.example.
  • Compare Maria's Country values across both sign-ins.
  • Compare Maria's DeviceId values across both sign-ins.
  • Check Maria's operating system and browser evidence.
  • Check Maria's authentication result in the Action column.
  • Look for any Audit row after Maria's Spain sign-in.

Maria's country changes from the United Kingdom to Spain. Her device remains SALES-LT-04.

Her operating system remains Windows 11. Her browser remains Edge.

Both sign-ins show MFA satisfied. No persistence event follows the Spain sign-in.

Good catch. Maria's stable device and normal authentication give you enough continuity to classify the alert as legitimate travel.

Confirm the CEO takeover

Charlotte's timeline contains independent signals from the sign-in and audit tables. Their chronological correlation supports one continuous attack sequence.

  • Find Charlotte's earlier United Kingdom sign-ins in the results grid.
  • Confirm those sign-ins use CEO-LT-01 with Windows 11 and Edge.
  • Find Charlotte's later rows from 203.0.113.77.
  • Count the events with an MFA denied action.
  • Locate the later MFA approved event.
  • Locate the MFA method registered audit event.

Why does this confirm a takeover?

  • Charlotte's familiar sign-ins use CEO-LT-01 from the United Kingdom.
  • The suspicious sign-ins use an Unregistered Linux device with Firefox at 203.0.113.77.
  • Two denials followed by one approval form an MFA fatigue sequence.
  • The MFA method registered event establishes persistence shortly after access succeeds.

You have crossed the key threshold. Charlotte's alert now has corroborating evidence from two independent tables.

Before you rerun the query, which evidence should decide the CEO verdict: country alone or the sequence from device change through persistence?

  • Rerun the comparison by selecting Run or pressing Shift + Enter.

Maria's rows show Spain on SALES-LT-04 with MFA satisfied. No audit event follows.

Charlotte's rows show Unregistered, Linux, and Firefox at 203.0.113.77. Two MFA denied events precede MFA approved.

An MFA method registered audit event follows at 09:07. The timeline confirms Charlotte Reed's account takeover.

Your verdict now separates benign travel from a confirmed CEO takeover. Next, you'll pivot on 203.0.113.77 to scope the breach.

Scope the Breach and Deliver the Incident Report

The CEO evidence confirms one takeover. A complete incident still needs a defensible scope before containment can begin.

A shared-infrastructure pivot in Kusto Query Language (KQL) reveals every related identity. Your incident report then turns the timeline into decisions that responders can act on.

In this step, get ready to:
  • Pivot across all three evidence tables using the attacker IP.
  • Reconstruct the business email compromise staging sequence.
  • Deliver an incident report with scope and containment priorities.
Pivot on the attacker IP

An infrastructure pivot searches for other activity linked to a known attacker indicator. Here, the shared source IP connects the confirmed CEO session to activity involving another identity.

  • Return to Notepad from earlier.
  • Create a new file in Notepad.
  • Save the file as Documents\CloudoraInvestigation\scope-incident.kql.
  • Add the initial sign-in pivot to scope-incident.kql by pasting this query:
let AttackerIP = "203.0.113.77";
CloudoraSigninLogs
| where IPAddress == AttackerIP
| project
    TimeGenerated,
    User=UserPrincipalName,
    Source="Sign-in",
    Action=strcat(Result, " / ", AuthenticationDetails),
    IPAddress,
    Evidence=strcat(Country, " | ", DeviceId, " | ", OperatingSystem, " | ", Browser)
| sort by TimeGenerated asc

What does this query do?

  • The AttackerIP value keeps the known source indicator in one place.
  • The where filter keeps sign-in records from 203.0.113.77.
  • The project operator reshapes each record into investigation-ready columns.
  • The final sort places the sign-ins in chronological order.
  • Save scope-incident.kql.
  • Select the complete query in Notepad.
  • Copy the selected query.

Before you run this, consider whether the shared IP will reveal activity beyond Charlotte Reed.

  • Switch back to the Azure Data Explorer query editor from earlier.
  • Replace the current editor contents with the copied query.
  • Select Run.

You should see sign-in records for Charlotte Reed plus Daniel Wu. Both identities use the same synthetic attacker IP from an unregistered Linux and Firefox device.

Only seeing one identity?

Confirm that the query context still shows CloudoraLab. Check that AttackerIP contains 203.0.113.77.

If rows are still missing, rerun the saved synthetic sign-in data script from the earlier step. Help me troubleshoot missing identities in my KQL IP pivot.

Correlate audit and mailbox evidence

Sign-in records establish shared infrastructure. Audit events and mailbox actions reveal persistence plus business email compromise staging.

This two-part edit expands the working sign-in pivot into one union. Complete both edits before running the expanded query.

  • Return to scope-incident.kql in Notepad.
  • Find this table name near the top of the query:
CloudoraSigninLogs

What are you changing?

This line currently starts a single-table query. You will turn it into the first branch of a multi-table union.

  • Replace that table name with this opening structure:
union
(
    CloudoraSigninLogs

Why open a union?

The union operator combines rows from multiple table expressions. The opening parenthesis marks the sign-in branch as the first expression.

  • Find the final | sort by TimeGenerated asc line.
  • Insert the audit and mailbox branches immediately above that line by pasting this code:
),
(
    CloudoraAuditLogs
    | where IPAddress == AttackerIP
    | project
        TimeGenerated,
        User=TargetUser,
        Source="Audit",
        Action=OperationName,
        IPAddress,
        Evidence=Details
),
(
    CloudoraMailboxLogs
    | where IPAddress == AttackerIP
    | project
        TimeGenerated,
        User=UserPrincipalName,
        Source="Mailbox",
        Action=Operation,
        IPAddress,
        Evidence=strcat(Recipient, " | ", Subject, " | ", Details)
)

How does the correlation work?

  • The audit branch reveals authentication persistence plus application consent from the attacker IP.
  • The mailbox branch reveals actions taken after authentication succeeded.
  • Each branch produces the same six columns so the union forms one readable timeline.
  • Chronological sorting exposes the attack sequence across identity and mailbox evidence.
  • Save scope-incident.kql.
  • Copy the complete query from Notepad.
  • Switch back to the Azure Data Explorer query editor.
  • Replace the editor contents with the expanded query.

Before you run the expanded query, consider what mailbox behavior would turn suspicious access into evidence of fraud staging.

  • Select Run.

You should see a chronological timeline containing charlotte.reed@cloudora.example plus daniel.wu@cloudora.example. The timeline now connects authentication activity to persistence and mailbox actions.

  • Find Charlotte Reed's Mailbox opened row.
  • Find the Inbox rule created row containing external-payments@example.net.
  • Find the Draft created row for updated supplier bank details.
  • Find the Message deleted row for the unusual sign-in alert.
  • Find Daniel Wu's Mailbox opened row.
  • Find Daniel Wu's Invoice search row.

These rows establish mailbox surveillance plus external forwarding. They also establish preparation for payment fraud.

The evidence does not show that the bank-details draft was sent. Your report must preserve that boundary between confirmed activity and potential impact.

Missing mailbox rows?

Confirm that the union includes CloudoraMailboxLogs. Check that its filter compares IPAddress with AttackerIP.

If the table has no matching data, rerun the saved mailbox data script from the earlier step. Help me troubleshoot missing mailbox evidence in my KQL union.

✔️ Awesome, I've got everything!

Strong work. Your scoped timeline now connects both compromised identities to the attacker's sign-in, persistence, and mailbox activity.

ⓧ I'd like to double check the full code

Compare your saved scope-incident.kql with this complete file:

let AttackerIP = "203.0.113.77";
union
(
    CloudoraSigninLogs
    | where IPAddress == AttackerIP
    | project
        TimeGenerated,
        User=UserPrincipalName,
        Source="Sign-in",
        Action=strcat(Result, " / ", AuthenticationDetails),
        IPAddress,
        Evidence=strcat(Country, " | ", DeviceId, " | ", OperatingSystem, " | ", Browser)
),
(
    CloudoraAuditLogs
    | where IPAddress == AttackerIP
    | project
        TimeGenerated,
        User=TargetUser,
        Source="Audit",
        Action=OperationName,
        IPAddress,
        Evidence=Details
),
(
    CloudoraMailboxLogs
    | where IPAddress == AttackerIP
    | project
        TimeGenerated,
        User=UserPrincipalName,
        Source="Mailbox",
        Action=Operation,
        IPAddress,
        Evidence=strcat(Recipient, " | ", Subject, " | ", Details)
)
| sort by TimeGenerated asc

What should match?

The complete query defines one attacker IP. It unions sign-in, audit, and mailbox projections before sorting the combined evidence by time.

Write the incident report

An incident report separates confirmed observations from potential impact. That distinction helps responders act quickly without overstating what the evidence proves.

  • Return to Notepad from earlier.
  • Create a new file in Notepad.
  • Save the file as Documents\CloudoraInvestigation\incident-report.md.
  • Add the executive summary and evidence timeline by pasting this content:
# Cloudora CEO Account Takeover Incident Report

## Executive summary

The investigation confirms a high-severity account takeover affecting CEO Charlotte Reed and finance director Daniel Wu. The same synthetic source IP and unregistered Linux/Firefox device appear in both identity trails. The activity progressed from MFA fatigue to successful access, authentication persistence, mailbox access, external forwarding, and preparation for payment fraud.

Maria Chen's Spain sign-in is a false positive. Her known device remained consistent, MFA succeeded normally, and no persistence or mailbox manipulation followed.

## Evidence and timeline

- Charlotte Reed received two denied MFA requests before one approval from an unregistered device.
- An alternate authentication method and mail-access consent appeared shortly after the successful CEO sign-in.
- The attacker opened the CEO mailbox, created an external forwarding rule, drafted an urgent bank-details message, and deleted a security notification.
- The same source IP then accessed Daniel Wu's account, registered another authentication method, opened the finance mailbox, and searched invoice records.

How does this section support the verdict?

The executive summary states the affected identities plus the overall attack progression. It also preserves Maria Chen as a documented false positive.

The timeline ties each conclusion to an observable event. This gives the verdict an evidence trail that another analyst can review.

  • Save incident-report.md.
  • Check the top of the file for the incident-report title.
  • Check the opening sections for Charlotte Reed and Daniel Wu.

The next section records impact plus standardized technique mappings. MITRE ATT&CK mappings give responders a shared vocabulary for the observed behavior.

  • Place the cursor after the final evidence timeline bullet.
  • Add the scope and mapping sections by pasting this content:


## Scope and business impact

Affected identities:

- charlotte.reed@cloudora.example
- daniel.wu@cloudora.example

Potential impact includes continued account persistence, executive-email surveillance, disclosure of finance correspondence, and business email compromise. The evidence shows fraud staging, but it does not show that the draft payment request was sent.

## MITRE ATT&CK mapping

- T1078 Valid Accounts: The attacker used successful authenticated sessions for the CEO and finance director.
- T1098 Account Manipulation: New authentication methods established persistence on both accounts.
- T1114.003 Email Forwarding Rule: An external inbox rule redirected finance-related messages.

Why include these mappings?

  • The T1078 mapping captures the successful use of compromised accounts.
  • The T1098 mapping captures the new authentication methods used for persistence.
  • The T1114.003 mapping captures the external email-forwarding rule.
  • The impact statement distinguishes staged fraud from a confirmed sent payment request.
  • Save incident-report.md.
  • Check the file for two affected identity addresses.
  • Check the mapping section for three technique identifiers.

Containment recommendations convert evidence into an ordered response. The priorities begin with cutting off access before preserving evidence and assessing financial exposure.

  • Place the cursor after the final ATT&CK mapping.
  • Complete the report by pasting the containment and verdict sections:


## Containment recommendations

1. Revoke active sessions and reset credentials for both affected identities through a trusted administrative process.
2. Remove unauthorized authentication methods, application consent, and the external forwarding rule.
3. Preserve the relevant identity, audit, and mailbox evidence before remediation changes the environment.
4. Review drafts, deleted items, sent mail, payment requests, and invoice activity for confirmed fraud or data exposure.
5. Alert finance staff through a trusted channel and verify any recent bank-detail changes independently.
6. Hunt for the attacker IP, unregistered-device pattern, and related persistence events across all identities.

## Verdict

Confirmed account takeover with persistence and business email compromise staging. Two identities are affected. One travel-related alert is a false positive. Immediate containment and finance-team notification are recommended.

How is the response prioritized?

The first actions remove attacker access plus persistence. Evidence preservation follows before broader fraud review and organization-wide hunting.

The verdict closes with confirmed scope and response urgency. It stays within what the synthetic evidence proves.

  • Save incident-report.md.
  • Check the final section for the confirmed account-takeover verdict.
  • Check the recommendations for six prioritized containment actions.

✔️ Awesome, I've got everything!

Your report is complete. It records the evidence-backed verdict while keeping confirmed activity separate from possible business impact.

ⓧ I'd like to double check the full code

Compare your saved incident-report.md with this complete report:

# Cloudora CEO Account Takeover Incident Report

## Executive summary

The investigation confirms a high-severity account takeover affecting CEO Charlotte Reed and finance director Daniel Wu. The same synthetic source IP and unregistered Linux/Firefox device appear in both identity trails. The activity progressed from MFA fatigue to successful access, authentication persistence, mailbox access, external forwarding, and preparation for payment fraud.

Maria Chen's Spain sign-in is a false positive. Her known device remained consistent, MFA succeeded normally, and no persistence or mailbox manipulation followed.

## Evidence and timeline

- Charlotte Reed received two denied MFA requests before one approval from an unregistered device.
- An alternate authentication method and mail-access consent appeared shortly after the successful CEO sign-in.
- The attacker opened the CEO mailbox, created an external forwarding rule, drafted an urgent bank-details message, and deleted a security notification.
- The same source IP then accessed Daniel Wu's account, registered another authentication method, opened the finance mailbox, and searched invoice records.

## Scope and business impact

Affected identities:

- charlotte.reed@cloudora.example
- daniel.wu@cloudora.example

Potential impact includes continued account persistence, executive-email surveillance, disclosure of finance correspondence, and business email compromise. The evidence shows fraud staging, but it does not show that the draft payment request was sent.

## MITRE ATT&CK mapping

- T1078 Valid Accounts: The attacker used successful authenticated sessions for the CEO and finance director.
- T1098 Account Manipulation: New authentication methods established persistence on both accounts.
- T1114.003 Email Forwarding Rule: An external inbox rule redirected finance-related messages.

## Containment recommendations

1. Revoke active sessions and reset credentials for both affected identities through a trusted administrative process.
2. Remove unauthorized authentication methods, application consent, and the external forwarding rule.
3. Preserve the relevant identity, audit, and mailbox evidence before remediation changes the environment.
4. Review drafts, deleted items, sent mail, payment requests, and invoice activity for confirmed fraud or data exposure.
5. Alert finance staff through a trusted channel and verify any recent bank-detail changes independently.
6. Hunt for the attacker IP, unregistered-device pattern, and related persistence events across all identities.

## Verdict

Confirmed account takeover with persistence and business email compromise staging. Two identities are affected. One travel-related alert is a false positive. Immediate containment and finance-team notification are recommended.

What should the complete report contain?

The final report names two compromised identities plus one false positive. It documents the attack timeline and business impact before mapping three techniques and prioritizing containment.

Before the final check, consider which timeline rows provide the strongest proof that Daniel Wu belongs inside the incident scope.

  • Switch back to the Azure Data Explorer query editor.
  • Run the complete contents of scope-incident.kql with Run.

You should see both charlotte.reed@cloudora.example and daniel.wu@cloudora.example in the sorted timeline. Daniel's successful access is followed by MFA persistence plus finance-mailbox activity.

  • Return to Notepad from earlier.
  • Select incident-report.md from your recent files.
  • Confirm that the report names the second affected account.
  • Confirm that the report records business email compromise staging.
  • Confirm that the report contains three ATT&CK mappings.
  • Confirm that the report contains prioritized containment actions.

You have completed the investigation package. The shared-infrastructure pivot now supports a two-account verdict plus a response-ready incident report.

Secret mission

Build a Correlation Detection

Turn the incident pattern into a reusable correlation query. You will match successful unregistered-device sign-ins to MFA persistence events within 15 minutes, then surface both compromised identities.

Clean Up Your Resources

Clean Up Your Resources

The lab is currently free because CloudoraSOC is an Azure Data Explorer free cluster. Pick the cleanup option that matches what you want to do next.

Resources you used:

  • A free Azure Data Explorer cluster named CloudoraSOC containing the CloudoraLab database plus three synthetic evidence tables.
  • The Documents\CloudoraInvestigation folder containing your KQL scripts plus incident-report.md.

Keep everything running

No action is needed. Choose this option if you want to rerun the investigation or extend the detection during the free-cluster period.

  • Retain CloudoraSOC for another investigation.
  • Keep Documents\CloudoraInvestigation as your evidence pack.
  • Use only synthetic data in the free cluster.
  • Leave Upgrade unselected to stay on the zero-billing path.

Pause - I'll come back to this later

Pausing preserves the lab without connecting a paid Azure resource. The cluster can remain idle while your local evidence stays in place.

  • Close Microsoft Edge.
  • Leave CloudoraSOC unchanged.
  • Keep Documents\CloudoraInvestigation in place.

Delete - I don't want to use this again

Remove the free cluster plus the local evidence pack if you no longer need the lab. Deleting CloudoraSOC permanently removes CloudoraLab plus all three synthetic tables.

Your incident report survives only if you already saved a separate copy.

  • Switch back to the Azure Data Explorer cluster overview for CloudoraSOC.
  • Select Delete on the cluster overview.
  • Complete the confirmation shown in Azure Data Explorer.
  • Return to My Cluster.

You should no longer see CloudoraSOC listed. This confirms that the cluster plus its database have been removed.

  • Use Windows search to open File Explorer.
  • Select Documents in the navigation pane.
  • Select the CloudoraInvestigation folder.
  • Press the Delete key to remove the selected folder.
  • Empty the Recycle Bin to remove the folder permanently.

You should no longer see CloudoraInvestigation in Documents or the Recycle Bin. Your local evidence pack is now deleted.

Nice Work!

Nice Work!

You made it! You completed a zero-billing account takeover investigation in Azure Data Explorer. You used Kusto Query Language (KQL) to model a Microsoft Sentinel investigation workflow with synthetic evidence.

You've learned how to:

  • Create a zero-billing investigation lab in Azure Data Explorer. You loaded three synthetic evidence tables for repeatable analysis.
  • Test legitimate travel through device continuity. You correlated MFA outcomes with persistence events to confirm the CEO takeover.
  • Pivot on shared attacker infrastructure to uncover a second compromised finance identity. You documented the resulting scope in an incident report. You mapped the evidence to MITRE ATT&CK. You prioritized containment in the report.
  • Secret Mission: Built a 15-minute KQL correlation detection that links successful access from unregistered devices to rapid MFA persistence.

Ready to quiz yourself?