Agent Foskett Academy • Lesson 123 • Advanced KQL Techniques

Advanced KQL: Mastering mv-expand.

Agent Foskett had found the evidence.

Unfortunately, it was all crammed into a single field.

One alert contained multiple devices, users, IP addresses and evidence records. Reading it as one dynamic object was painful. Then one operator transformed the investigation.

mv-expand turned the hidden collection into individual rows that could be filtered, searched, joined and understood.

Start Lesson 123 Back to Academy

Agent Foskett Academy lesson mastering mv-expand in KQL

Lesson overview

Learn how to use mv-expand to expand arrays and multi-value fields in Microsoft Defender XDR telemetry into investigation-ready rows.

Expand arrays into rows

Work with dynamic evidence

Combine with parse_json()

Join expanded evidence

Why mv-expand matters

Many Microsoft Defender XDR records contain more than one value inside a dynamic field. mv-expand makes each value easier to search, filter and correlate.

Arrays hide useful evidenceA single field might contain multiple users, IP addresses, devices, alert entities or authentication steps. Expanding the field turns hidden values into rows.

Rows are easier to investigateOnce each item becomes its own row, defenders can filter, summarize, join and project the evidence like any other table data.

Expansion supports correlationExpanded evidence can be joined with AlertInfo, DeviceProcessEvents, IdentityLogonEvents and other Defender XDR tables.

The mv-expand workflow

Agent Foskett uses a simple workflow: find the dynamic field, confirm it contains an array, expand it, extract the useful properties and then correlate the result.

1. Identify the multi-value fieldLook for fields containing arrays, JSON objects, evidence collections or repeated values.

2. Parse if neededIf the field is stored as text, convert it to dynamic data first using parse_json() or todynamic().

3. Expand the arrayUse mv-expand to create one output row for each item in the array.

4. Extract propertiesUse dot notation or project fields to turn expanded values into readable investigation columns.

5. Correlate resultsJoin expanded evidence to related alerts, users, devices, IP addresses or cloud activity.

6. Optimise the queryFilter early and expand only the records needed for the investigation.

Step 1 — Expand a simple array

This simple example shows the core idea. One array becomes multiple rows.

let SuspiciousUsers = dynamic(["alice@contoso.com", "bob@contoso.com", "charlie@contoso.com"]);
print Users = SuspiciousUsers
| mv-expand Users
| project User = tostring(Users)

Step 2 — Review alert evidence as rows

AlertEvidence is already event-like, but the mindset is the same: separate entities so each account, device and IP can be analysed clearly.

AlertEvidence
| where Timestamp > ago(7d)
| where isnotempty(AccountName) or isnotempty(DeviceName) or isnotempty(RemoteIP)
| project Timestamp,
          AlertId,
          EntityType,
          EvidenceRole,
          AccountName,
          DeviceName,
          RemoteIP
| order by Timestamp desc

Step 3 — Expand authentication details

Authentication details often contain multiple steps. Expanding them makes MFA methods, step results and authentication requirements easier to inspect.

SigninLogs
| where TimeGenerated > ago(7d)
| where isnotempty(AuthenticationDetails)
| mv-expand AuthenticationDetails
| extend AuthStep = tostring(AuthenticationDetails.authenticationStepResultDetail),
         AuthMethod = tostring(AuthenticationDetails.authenticationMethod),
         AuthRequirement = tostring(AuthenticationDetails.authenticationStepRequirement)
| project TimeGenerated,
          UserPrincipalName,
          IPAddress,
          Location,
          AuthMethod,
          AuthRequirement,
          AuthStep
| order by TimeGenerated desc

Step 4 — Combine parse_json() with mv-expand

When a field is stored as JSON text, parse it first. Then expand collections only when the parsed object contains multiple values.

let TimeFrame = 7d;
CloudAppEvents
| where Timestamp > ago(TimeFrame)
| where isnotempty(RawEventData)
| extend ParsedEvent = parse_json(RawEventData)
| mv-expand ParsedEvent
| project Timestamp,
          AccountDisplayName,
          ActionType,
          Application,
          ParsedEvent

Step 5 — Expand, then summarise

After expanding an array, summarise the result to build clean user, device or alert summaries.

SigninLogs
| where TimeGenerated > ago(14d)
| where isnotempty(AuthenticationDetails)
| mv-expand AuthenticationDetails
| extend AuthMethod = tostring(AuthenticationDetails.authenticationMethod)
| summarize AuthMethods = make_set(AuthMethod, 20),
            SignInCount = count(),
            FirstSeen = min(TimeGenerated),
            LastSeen = max(TimeGenerated)
      by UserPrincipalName
| order by SignInCount desc

Step 6 — Join expanded evidence with alert context

Expanded or entity-level evidence becomes more useful when joined back to alert titles, severity and categories.

let RecentAlerts =
    AlertInfo
    | where Timestamp > ago(7d)
    | project AlertId, Title, Severity, Category;
AlertEvidence
| where Timestamp > ago(7d)
| where isnotempty(AccountName)
| join kind=inner RecentAlerts on AlertId
| project Timestamp,
          Title,
          Severity,
          Category,
          AccountName,
          DeviceName,
          RemoteIP,
          EvidenceRole
| order by Timestamp desc

Step 7 — Build a reusable mv-expand pattern

Store expanded evidence in a let statement so the rest of the investigation can reuse clean, readable rows.

let TimeFrame = 7d;
let ExpandedAuth =
    SigninLogs
    | where TimeGenerated > ago(TimeFrame)
    | where isnotempty(AuthenticationDetails)
    | mv-expand AuthenticationDetails
    | extend AuthMethod = tostring(AuthenticationDetails.authenticationMethod),
             AuthResult = tostring(AuthenticationDetails.authenticationStepResultDetail)
    | project TimeGenerated, UserPrincipalName, IPAddress, Location, AuthMethod, AuthResult;
ExpandedAuth
| where AuthMethod has_any ("SMS", "Phone", "Authenticator")
| summarize Methods = make_set(AuthMethod, 20),
            Results = make_set(AuthResult, 20),
            IPAddresses = make_set(IPAddress, 50),
            Locations = make_set(Location, 20)
      by UserPrincipalName
| order by UserPrincipalName asc

Common mv-expand mistakes

Expansion is powerful, but it can create noisy or expensive queries when used carelessly.

Expanding too earlyFilter the dataset first. Expanding millions of rows before filtering can make a query slow and noisy.

Expanding the wrong fieldConfirm the column actually contains an array or dynamic collection before using mv-expand.

Forgetting type conversionExpanded values may need tostring(), toint(), todatetime() or other conversions before filtering or joining.

Creating duplicate-looking rowsExpansion creates one row per array item. This can look like duplication unless you understand the source collection.

Joining without cleaning keysConvert expanded values into clean columns before joining them to other tables.

Ignoring performanceProject only required fields and avoid expanding dynamic objects that are not relevant to the investigation.

Investigation checklist

Use this checklist when working with arrays and dynamic collections in KQL.

Identify the arrayFind the dynamic or multi-value field that contains the hidden evidence.

Parse if requiredUse parse_json() when the field is stored as text rather than dynamic data.

Expand carefullyApply mv-expand only after filtering to the investigation timeframe and relevant records.

Extract propertiesTurn expanded objects into named columns using dot notation and type conversion.

Validate rowsCheck that the expanded rows represent the evidence you expected.

Correlate evidenceJoin expanded rows with related Defender XDR tables to build richer investigations.

Related Agent Foskett Academy lessons

These lessons support advanced mv-expand investigations.

Advanced KQL: Mastering Join OperationsCorrelate expanded evidence with related Microsoft Defender XDR tables.

Advanced KQL: Working with Dynamic DataUnderstand dynamic objects, JSON fields and nested telemetry before expanding arrays.

Building Reusable Hunting QueriesUse let statements to keep expanded evidence reusable and readable.

Investigating AlertEvidenceUnderstand how alert evidence links users, devices, IP addresses and other entities.

Investigating CloudAppEventsWork with cloud activity fields that often contain complex event data.

Using mv-expand in KQLReview the earlier foundation lesson before moving into advanced expansion patterns.

Coming next

The Advanced KQL series continues with performance optimisation.

Lesson 124 — Advanced KQL: Optimising Query PerformanceNext, Agent Foskett explains how to make Microsoft Defender XDR hunting queries faster, cleaner and safer at enterprise scale.

Why this mattersAdvanced hunters need powerful queries, but they also need queries that run efficiently across large security datasets.

Final thought

mv-expand turns hidden collections into investigation evidence.

Agent Foskett mindsetWhen evidence is trapped inside an array, do not ignore it. Expand it, inspect it and correlate it with the rest of the investigation.

Advanced KQL TechniquesLesson 123 connects dynamic data, arrays and joins into a practical workflow for Microsoft Defender XDR hunting.

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

Advanced KQL Mastering mv-expand

Agent Foskett Academy Lesson 123 teaches defenders how to use mv-expand in KQL to expand arrays, dynamic fields, authentication details, alert evidence and Microsoft Defender XDR telemetry into searchable investigation rows.

Learn mv-expand for Microsoft Defender XDR hunting

mv-expand helps security analysts investigate dynamic arrays, multi-value fields, JSON data and nested evidence collections across Microsoft Defender XDR and Microsoft Sentinel.

KQL mv-expand investigation tutorial

This lesson explains how to combine mv-expand with parse_json(), joins, summarise patterns and reusable let statements to build advanced Microsoft security hunting queries.