ACSC Logo
© 2026 GEMXIT
GEMXIT Logo
Agent Foskett Academy • Lesson 98 • Advanced KQL

Using mv-apply in KQL.

The array was full of evidence.

But expanding every value created too much noise.

Agent Foskett did not need every item.

He needed controlled expansion, filtering and analysis inside the multi-value field itself.

Start Lesson 98 Back to Academy
Agent Foskett Academy lesson using mv-apply in KQL for Microsoft Defender XDR investigations
Lesson overview

Learn how defenders use mv-apply to analyse arrays and dynamic values with more control than mv-expand, helping Microsoft Defender XDR investigations filter, summarise and extract only the evidence that matters.

Understand mv-apply
Compare mv-apply and mv-expand
Filter inside multi-value fields
Build controlled array investigations

Why mv-apply matters

mv-expand is excellent when every array value deserves its own row. mv-apply is better when defenders need to run a subquery against each multi-value field and keep the investigation controlled.
Arrays can be noisyA single event may contain many values, but not every value is useful. mv-apply helps analyse the array before returning results.
Filtering becomes more preciseInstead of expanding everything first, defenders can filter, project and summarise values inside the mv-apply subquery.
Complex evidence becomes readableNested objects, indicator lists and dynamic arrays can be transformed into cleaner investigation output.

The mv-apply investigation workflow

Agent Foskett uses mv-apply when an event contains a collection of values and the investigation needs control over how those values are inspected.
1. Identify the arrayFind the dynamic column or property that contains multiple values, such as indicators, recipients, URLs or device evidence.
2. Apply mv-applyUse mv-apply to run a subquery over each array while preserving context from the original event.
3. Filter inside the subqueryApply where conditions inside mv-apply so only relevant values are returned.
4. Extract useful propertiesUse tostring(), toint() and project to turn nested values into readable investigation columns.
5. Summarise when neededUse summarize inside or after mv-apply to reduce the output into investigation-focused evidence.
6. Keep original contextReturn the timestamp, user, device and action alongside the extracted multi-value evidence.

Step 1 — Basic mv-apply pattern

This example applies a subquery to each value in a dynamic array and returns each value with the original device context.
step-1-basic-mv-apply.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
DeviceEvents
| where Timestamp > ago(24h)
| where isnotempty(AdditionalFields)
| extend Details = parse_json(AdditionalFields)
| extend Indicators = Details.Indicators
| mv-apply Indicator = Indicators on (
    project IndicatorValue = tostring(Indicator.Value),
            IndicatorType = tostring(Indicator.Type)
)
| project Timestamp,
          DeviceName,
          ActionType,
          IndicatorType,
          IndicatorValue
| order by Timestamp desc

Step 2 — Filter values inside mv-apply

The advantage of mv-apply is that defenders can filter the array contents before returning the final rows.
step-2-filter-inside-mv-apply.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
DeviceEvents
| where Timestamp > ago(7d)
| extend Details = parse_json(AdditionalFields)
| extend Indicators = Details.Indicators
| mv-apply Indicator = Indicators on (
    extend IndicatorType = tostring(Indicator.Type)
    | extend IndicatorValue = tostring(Indicator.Value)
    | where IndicatorType in ("Url", "Domain", "IpAddress")
    | project IndicatorType, IndicatorValue
)
| project Timestamp,
          DeviceName,
          ActionType,
          IndicatorType,
          IndicatorValue

Step 3 — Analyse suspicious URLs

Use mv-apply to inspect only URL values from a multi-value field and return the suspicious ones.
step-3-suspicious-url-array.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
DeviceEvents
| where Timestamp > ago(7d)
| extend Details = parse_json(AdditionalFields)
| extend Urls = Details.Urls
| mv-apply UrlItem = Urls on (
    extend Url = tostring(UrlItem)
    | where Url has_any (".zip", ".top", ".xyz", "login", "verify")
    | project Url
)
| project Timestamp,
          DeviceName,
          ActionType,
          Url
| order by Timestamp desc

Step 4 — Summarise values returned from arrays

After using mv-apply, summarise the extracted values so the investigation shows scope rather than raw noise.
step-4-summarise-mv-apply-results.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
DeviceEvents
| where Timestamp > ago(7d)
| extend Details = parse_json(AdditionalFields)
| extend Indicators = Details.Indicators
| mv-apply Indicator = Indicators on (
    extend IndicatorValue = tostring(Indicator.Value)
    | where isnotempty(IndicatorValue)
    | project IndicatorValue
)
| summarize IndicatorCount = dcount(IndicatorValue),
            IndicatorSet = make_set(IndicatorValue, 25)
      by DeviceName,
         ActionType
| order by IndicatorCount desc

Step 5 — Compare mv-expand and mv-apply

mv-expand returns each value as a row. mv-apply lets the query filter and shape the array values before the final result is returned.
step-5-mv-expand-vs-mv-apply.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
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
// mv-expand: expand everything, then filter
DeviceEvents
| where Timestamp > ago(24h)
| extend Details = parse_json(AdditionalFields)
| extend Indicators = Details.Indicators
| mv-expand Indicators
| extend IndicatorValue = tostring(Indicators.Value)
| where IndicatorValue has "malicious"
| project Timestamp, DeviceName, IndicatorValue;

// mv-apply: filter inside the array subquery
DeviceEvents
| where Timestamp > ago(24h)
| extend Details = parse_json(AdditionalFields)
| extend Indicators = Details.Indicators
| mv-apply Indicator = Indicators on (
    extend IndicatorValue = tostring(Indicator.Value)
    | where IndicatorValue has "malicious"
    | project IndicatorValue
)
| project Timestamp, DeviceName, IndicatorValue

Step 6 — Controlled IOC investigation

This pattern is useful when a Defender XDR event contains several possible indicators but the analyst only wants values that match a known IOC list.
step-6-controlled-ioc-investigation.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
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
let KnownBadIndicators = dynamic([
    "evil.example",
    "bad-domain.top",
    "203.0.113.10",
    "malicious-file.zip"
]);
DeviceEvents
| where Timestamp > ago(14d)
| extend Details = parse_json(AdditionalFields)
| extend Indicators = Details.Indicators
| mv-apply Indicator = Indicators on (
    extend IndicatorValue = tostring(Indicator.Value)
    | where IndicatorValue in~ (KnownBadIndicators)
    | project IndicatorValue
)
| project Timestamp,
          DeviceName,
          ActionType,
          AccountName,
          IndicatorValue
| order by Timestamp desc

When to use mv-expand versus mv-apply

Use mv-expand for simple expansionChoose mv-expand when the investigation needs every value from the array as its own row.
Use mv-apply for controlled analysisChoose mv-apply when you need to filter, project, summarise or transform the array values inside a subquery.
Use mv-apply for nested objectsWhen array items contain properties such as Type, Value or Severity, mv-apply helps extract only the relevant fields.
Keep performance in mindDo not expand large arrays unnecessarily. Filter by time, action type and entity before applying multi-value operators.
Project clean outputAfter analysing the array, return only the columns that help explain the investigation.
Validate with small samplesBefore running a broad query, use take or a tight time window to confirm the array structure.

Real-world investigation

The noisy alertAn alert includes a dynamic list of URLs, IP addresses and file indicators. Expanding every value creates too many rows.
The controlled approachAgent Foskett uses mv-apply to inspect the indicator array and keep only domains, URLs and IP addresses that match suspicious patterns.
The evidence extractionThe query converts nested Type and Value properties into clear columns that can be sorted, summarised and explained.
The scope checkUsing summarize and make_set(), the investigation shows which devices touched the suspicious indicators.
The findingOnly a small number of array values mattered, but they revealed the devices and accounts that needed immediate attention.
The lessonMulti-value fields are powerful, but defenders need control. mv-apply gives that control when mv-expand is too broad.

Investigation checklist

Confirm the field is dynamicInspect the source field first so you know whether it contains an array, object or simple value.
Filter before applyingReduce the dataset with Timestamp, ActionType, DeviceName, AccountName or other known fields first.
Extract inside the subqueryUse extend and project inside mv-apply to shape the array values before returning them.
Avoid returning raw JSONConvert nested values into normal columns so the output is easy to read and explain.
Summarise for scopeUse dcount(), make_set() and count() to understand how widespread the evidence is.
Use mv-expand when simplerDo not use mv-apply just because it is advanced. Use the simplest operator that solves the investigation.

Related Agent Foskett Academy lessons

Using mv-expand in KQLReview the previous lesson that explains how to split arrays and multi-value fields into individual rows.
Working with Dynamic Data and JSON in KQLLearn how to parse JSON and extract useful values from dynamic Defender XDR fields.
Using make_set() in KQLBuild unique collections of extracted values during investigation summaries.
Using arg_max() in KQLReturn the latest event for each entity during Microsoft Defender XDR investigations.
Using let StatementsCreate reusable values and query blocks for controlled hunting workflows.
Building an IOC HuntUse extracted indicators to hunt across endpoint, email and identity telemetry.

Coming next

Lesson 99 — Regular Expressions in KQLNext, Agent Foskett Academy explores how defenders use regex to extract, match and investigate complex text patterns in Microsoft Defender XDR telemetry.
Why this mattersmv-apply gives defenders control over multi-value fields. Regex gives them control over messy strings, command lines, URLs and suspicious text patterns.

Final thought

mv-apply is not about making KQL look more advanced. It is about making noisy multi-value evidence easier to control.
Agent Foskett mindsetExpand only what you need, filter as close to the evidence as possible, and keep the final result readable.
Control beats noiseWhen an array contains too much information, mv-apply helps the analyst return the part that actually matters.
Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD

Using mv-apply in KQL

Agent Foskett Academy Lesson 98 teaches defenders how to use mv-apply in KQL to analyse arrays, dynamic fields and multi-value evidence during Microsoft Defender XDR investigations.

Learn mv-apply for Microsoft Defender XDR

The KQL mv-apply operator helps Microsoft security analysts filter, project and summarise values inside arrays and dynamic objects without expanding unnecessary evidence.

KQL mv-apply tutorial for Microsoft security analysts

This lesson explains mv-apply, mv-expand comparison, dynamic arrays, nested objects, IOC matching and controlled threat hunting patterns for Microsoft Defender XDR telemetry.

ACSC Logo

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT 2026