Agent Foskett Academy • Lesson 97 • Advanced KQL

Using mv-expand in KQL.

The evidence was all there.

But it was packed into one field.

One event. Many values. Too many clues hidden in the same row.

Agent Foskett knew the investigation needed each value to stand on its own.

Start Lesson 97 Back to Academy

Agent Foskett Academy lesson using mv-expand in KQL for Microsoft Defender XDR investigations

Lesson overview

Learn how to use mv-expand to split arrays and multi-value fields into individual rows, making Microsoft Defender XDR evidence easier to filter, summarise and investigate.

Understand multi-value fields

Expand arrays into rows

Extract values from dynamic JSON

Build cleaner investigations

Why mv-expand matters

Microsoft Defender XDR telemetry often stores multiple values inside a single dynamic field. mv-expand helps defenders turn those packed values into rows that can be filtered, counted and hunted.

One row can hide many cluesA single event may contain several indicators, recipients, URLs, IP addresses or nested objects.

Rows are easier to investigateOnce each value has its own row, defenders can filter, summarise and correlate the evidence more easily.

Dynamic data becomes searchablemv-expand helps transform arrays into investigation-ready records instead of leaving them trapped inside JSON.

The mv-expand investigation workflow

Agent Foskett treats mv-expand as a controlled way to unpack evidence. Inspect the dynamic field first, expand only what matters, then project a clean output.

1. Find the arrayIdentify the dynamic field that contains multiple values.

2. Parse when requiredUse parse_json() or todynamic() if the field is stored as JSON text.

3. Expand the fieldUse mv-expand to create one row for each value.

4. Extract propertiesIf each array item is an object, pull out the useful properties.

5. Convert valuesUse tostring(), toint() or todatetime() so fields behave correctly.

6. Summarise the resultsCount and group the expanded records to understand scope and impact.

Step 1 — Inspect the dynamic field

Before expanding anything, look at the raw field so you understand the structure and available values.

DeviceEvents
| where Timestamp > ago(24h)
| where isnotempty(AdditionalFields)
| project Timestamp,
          DeviceName,
          ActionType,
          AdditionalFields
| take 20

Step 2 — Expand a simple dynamic array

When a field already contains an array, mv-expand creates one row for each item in that array.

DeviceEvents
| where Timestamp > ago(24h)
| extend Details = parse_json(AdditionalFields)
| extend Indicators = Details.Indicators
| mv-expand Indicators
| project Timestamp,
          DeviceName,
          ActionType,
          Indicators

Step 3 — Extract properties after expanding

After expansion, each item can be treated like its own dynamic object. Extract the properties that matter to the investigation.

DeviceEvents
| where Timestamp > ago(24h)
| extend Details = parse_json(AdditionalFields)
| extend Indicators = Details.Indicators
| mv-expand Indicators
| extend IndicatorType = tostring(Indicators.Type)
| extend IndicatorValue = tostring(Indicators.Value)
| project Timestamp,
          DeviceName,
          ActionType,
          IndicatorType,
          IndicatorValue
| order by Timestamp desc

Step 4 — Expand email recipients

Use mv-expand when one email-related record contains multiple recipients or addresses that need to be reviewed separately.

EmailEvents
| where Timestamp > ago(7d)
| where isnotempty(RecipientEmailAddress)
| summarize Recipients = make_set(RecipientEmailAddress, 100)
      by NetworkMessageId,
         SenderFromAddress,
         Subject
| mv-expand Recipients
| extend Recipient = tostring(Recipients)
| project NetworkMessageId,
          SenderFromAddress,
          Recipient,
          Subject

Step 5 — Expand indicators for IOC hunting

Expand a list of indicators so each value can be joined, filtered or hunted across other Defender XDR tables.

let IOCs = dynamic([
    "contoso-login.example",
    "198.51.100.25",
    "malicious-file.exe"
]);
print Indicator = IOCs
| mv-expand Indicator
| extend Indicator = tostring(Indicator)
| join kind=inner (
    DeviceNetworkEvents
    | where Timestamp > ago(7d)
    | project Timestamp,
              DeviceName,
              RemoteUrl,
              RemoteIP
) on $left.Indicator == $right.RemoteUrl
| project Timestamp,
          DeviceName,
          Indicator,
          RemoteUrl,
          RemoteIP

Step 6 — Summarise expanded values

Once values are expanded, summarise them to understand which users, devices or indicators appear most often.

DeviceEvents
| where Timestamp > ago(7d)
| extend Details = parse_json(AdditionalFields)
| mv-expand Indicator = Details.Indicators
| extend IndicatorValue = tostring(Indicator.Value)
| where isnotempty(IndicatorValue)
| summarize Events = count(),
            Devices = make_set(DeviceName, 20),
            Actions = make_set(ActionType, 20)
      by IndicatorValue
| order by Events desc

mv-expand patterns to remember

mv-expand is powerful, but it should be used deliberately. Expand the field you need, then quickly clean up the output.

Inspect firstDo not expand a field until you know whether it is an array, object or plain text.

Expand one thing at a timeExpanding multiple fields can multiply rows quickly and make results noisy.

Convert after expansionUse tostring() or other conversion functions so expanded values can be filtered and summarised.

Project clean outputRemove the raw JSON once the useful fields have been extracted.

Watch row growthmv-expand can create many rows from a small number of events.

Summarise after expandingUse summarize to turn expanded evidence back into investigation scope.

Real-world investigation

Agent Foskett uses mv-expand when a single event contains too much evidence to understand in one row.

The packed alertAn alert contains a dynamic field with several indicators, but the visible columns only show one event.

The first inspectionAgent Foskett reviews the raw AdditionalFields value and finds an array of indicator objects.

The expansionmv-expand turns each indicator into its own row so the values can be reviewed individually.

The extractionEach expanded item is converted into IndicatorType and IndicatorValue columns.

The scope checkThe expanded indicators are summarised by device and action to understand where they appeared.

The resultThe investigation becomes clear: one alert contained multiple indicators that needed separate hunting paths.

Investigation checklist

Use this checklist when adding mv-expand to Microsoft Defender XDR hunting queries.

Confirm the field contains multiple valuesUse project and take before expanding the data.

Parse JSON if neededUse parse_json() when the field is stored as JSON text.

Expand the correct fieldTarget the array or multi-value property that contains the evidence.

Extract useful propertiesPull out values such as Type, Value, Name, URL, IP address or user.

Control the outputProject only the columns needed for the next step of the investigation.

Summarise the expanded evidenceCount and group the expanded rows to understand scope and repetition.

Related Agent Foskett Academy lessons

These lessons connect directly with mv-expand and advanced KQL investigations.

Working with Dynamic Data and JSON in KQLParse and extract values from JSON and dynamic Defender XDR fields.

Using make_set() in KQLBuild unique evidence sets before or after expansion.

Using arg_max() in KQLReturn the latest event per user, device or indicator.

Using let StatementsCreate reusable query blocks for complex hunting logic.

Advanced join Techniques in KQLCorrelate expanded indicators with other Defender XDR tables.

Building an IOC HuntUse extracted indicators to hunt across the environment.

Coming next

Lesson 98 moves from mv-expand to mv-apply, giving defenders more control when working with multi-value data.

Lesson 98 — Using mv-apply in KQLNext, Agent Foskett Academy explains how mv-apply can filter, summarise and process multi-value data with more control than a simple expansion.

Why this mattersmv-expand is excellent for splitting values into rows. mv-apply helps when each expanded set needs its own mini-query.

Final thought

Multi-value evidence is only useful when defenders can separate it, read it and explain it.

Agent Foskett mindsetDo not let useful evidence stay trapped inside a dynamic array.

Expand with purposeUse mv-expand to reveal hidden values, then summarise them into investigation scope.

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

Using mv-expand in KQL

Agent Foskett Academy Lesson 97 teaches defenders how to use mv-expand in KQL to split arrays and multi-value fields into individual rows during Microsoft Defender XDR investigations.

Learn mv-expand for Microsoft Defender XDR

The mv-expand operator helps Microsoft security analysts unpack dynamic data, expand indicators, separate email recipients and analyse hidden values in Defender XDR telemetry.

KQL mv-expand tutorial for threat hunting

This lesson explains how to inspect dynamic fields, parse JSON, expand arrays, extract properties and summarise expanded evidence during Microsoft Defender XDR threat hunting.