Agent Foskett Academy • Lesson 122 • Advanced KQL Series

Advanced KQL: Working with Dynamic Data.

The investigation seemed simple.

Agent Foskett had the alert, the user, the device and the timestamp.

But the most important evidence was buried inside a dynamic field full of JSON, arrays and nested properties.

The data was not missing. It was just hidden inside a structure that needed to be parsed, expanded and shaped into useful investigation evidence.

Start Lesson 122 Back to Academy

Agent Foskett Academy lesson working with dynamic data in KQL

Lesson overview

Learn how to work with KQL dynamic data, JSON objects, arrays and nested Microsoft Defender XDR fields so complex telemetry becomes readable investigation evidence.

Understand dynamic fields

Parse JSON safely

Expand arrays into rows

Extract nested investigation evidence

Why dynamic data matters

Modern Microsoft security telemetry often stores rich evidence inside JSON-like dynamic fields rather than simple columns.

Evidence can be nestedAuthentication details, alert entities, cloud app records and additional fields may contain objects inside objects.

Arrays need expandingSome fields contain lists of users, IP addresses, permissions, devices or evidence items that need to become separate rows.

Parsing reveals contextOnce dynamic data is parsed correctly, hidden evidence becomes searchable, filterable and useful during investigations.

The dynamic data workflow

Agent Foskett treats dynamic fields as evidence containers. The goal is to safely extract only the details needed for the investigation.

1. Identify the dynamic fieldFind columns such as AdditionalFields, AuthenticationDetails, RawEventData, Entities or extended event properties.

2. Parse the objectUse parse_json() or todynamic() when the field needs to be converted into a usable dynamic object.

3. Extract propertiesUse dot notation or bracket notation to pull specific values such as user, IP address, device, permission or operation.

4. Expand arraysUse mv-expand when one event contains multiple evidence values that need to be analysed individually.

5. Flatten when usefulUse bag_unpack() when property bags need to become readable columns for investigation or reporting.

6. Optimise the queryFilter early, parse once, project only required fields and avoid expanding large arrays until necessary.

Step 1 — Identify dynamic-looking fields

This query demonstrates a practical dynamic-data technique that turns complex fields into usable investigation evidence.

1
2
3
4
5
6
7
8
9
10
11

CloudAppEvents
| where Timestamp > ago(7d)
| project Timestamp,
          AccountDisplayName,
          ActionType,
          Application,
          RawEventData,
          AdditionalFields
| take 25

Step 2 — Parse a JSON field

This query demonstrates a practical dynamic-data technique that turns complex fields into usable investigation evidence.

1
2
3
4
5
6
7
8
9
10

CloudAppEvents
| where Timestamp > ago(7d)
| extend ParsedEvent = parse_json(RawEventData)
| project Timestamp,
          AccountDisplayName,
          ActionType,
          Application,
          ParsedEvent

Step 3 — Extract nested properties

This query demonstrates a practical dynamic-data technique that turns complex fields into usable investigation evidence.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CloudAppEvents
| where Timestamp > ago(7d)
| extend ParsedEvent = parse_json(RawEventData)
| extend SourceIP = tostring(ParsedEvent.ClientIP),
         UserAgent = tostring(ParsedEvent.UserAgent),
         Operation = tostring(ParsedEvent.Operation)
| project Timestamp,
          AccountDisplayName,
          Application,
          SourceIP,
          UserAgent,
          Operation
| order by Timestamp desc

Step 4 — Expand arrays into rows

This query demonstrates a practical dynamic-data technique that turns complex fields into usable investigation evidence.

1
2
3
4
5
6
7
8
9
10
11
12
13

AlertEvidence
| where Timestamp > ago(7d)
| summarize EvidenceTypes = make_set(EntityType, 50),
            EvidenceRoles = make_set(EvidenceRole, 50),
            Devices = make_set(DeviceName, 50)
      by AlertId
| mv-expand EvidenceTypes
| project AlertId,
          EvidenceType = tostring(EvidenceTypes),
          EvidenceRoles,
          Devices

Step 5 — Work with AuthenticationDetails

This query demonstrates a practical dynamic-data technique that turns complex fields into usable investigation evidence.

1
2
3
4
5
6
7
8
9
10
11
12
13

EmailEvents
| where Timestamp > ago(7d)
| where isnotempty(AuthenticationDetails)
| extend Auth = parse_json(AuthenticationDetails)
| project Timestamp,
          SenderFromAddress,
          SenderIPv4,
          NetworkMessageId,
          AuthenticationDetails,
          Auth
| take 50

Step 6 — Create investigation-ready columns

This query demonstrates a practical dynamic-data technique that turns complex fields into usable investigation evidence.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

EmailEvents
| where Timestamp > ago(7d)
| where isnotempty(AuthenticationDetails)
| extend Auth = parse_json(AuthenticationDetails)
| extend SPF = tostring(Auth.SPF),
         DKIM = tostring(Auth.DKIM),
         DMARC = tostring(Auth.DMARC)
| project Timestamp,
          SenderFromAddress,
          SenderIPv4,
          NetworkMessageId,
          SPF,
          DKIM,
          DMARC,
          DeliveryAction
| order by Timestamp desc

Step 7 — Build a reusable dynamic-data pattern

This query demonstrates a practical dynamic-data technique that turns complex fields into usable investigation evidence.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

let TimeFrame = 7d;
CloudAppEvents
| where Timestamp > ago(TimeFrame)
| extend ParsedEvent = parse_json(RawEventData)
| extend SourceIP = tostring(ParsedEvent.ClientIP),
         UserAgent = tostring(ParsedEvent.UserAgent),
         Operation = tostring(ParsedEvent.Operation)
| where isnotempty(SourceIP) or isnotempty(Operation)
| project Timestamp,
          AccountDisplayName,
          Application,
          ActionType,
          SourceIP,
          UserAgent,
          Operation
| order by Timestamp desc

Common dynamic-data mistakes

Dynamic fields are powerful, but they can create noisy or slow queries when handled carelessly.

Parsing too earlyFilter by time, table and obvious conditions before parsing large JSON fields.

Expanding everythingmv-expand can multiply rows quickly. Expand only the arrays that answer the investigation question.

Assuming properties always existUse tostring(), isnotempty() and careful projection because dynamic objects can vary between events.

Forgetting type conversionValues extracted from dynamic objects often need tostring(), toint(), todatetime() or another conversion before comparison.

Keeping too many columnsProject only the fields needed for the investigation so the results remain readable.

Treating JSON as noiseMany of the most important clues are hidden in dynamic fields. Do not ignore them just because they are messy.

Investigation checklist

Use this checklist whenever a Microsoft Defender XDR investigation depends on dynamic fields.

Identify the evidence containerFind the dynamic or JSON field that contains the details you need.

Parse onceCreate a parsed object with extend so you can reuse it cleanly throughout the query.

Extract only useful propertiesTurn nested values into readable columns such as SourceIP, Operation, Permission, DeviceName or UserAgent.

Expand arrays carefullyUse mv-expand only when each array item needs to become its own evidence row.

Validate the outputCheck that the extracted values match the original raw field before relying on the results.

Optimise before savingFilter early, project narrow, and avoid unnecessary parsing in reusable hunting queries.

Related Agent Foskett Academy lessons

These lessons support advanced dynamic-data investigations.

Advanced KQL: Mastering Join OperationsCombine parsed dynamic evidence with related data from other Defender XDR tables.

Using parse_json() in KQLReview the foundational approach to parsing JSON fields.

Using mv-expand in KQLExpand arrays and repeated evidence values into separate rows.

Investigating AlertEvidenceWork with alert entities and evidence relationships.

Investigating CloudAppEventsExplore cloud activity records that commonly include rich dynamic properties.

Building Reusable Hunting QueriesTurn dynamic-data extraction into repeatable investigation logic.

Coming next

The Advanced KQL series continues with deeper array handling.

Lesson 123 — Advanced KQL: Advanced mv-expand TechniquesNext, Agent Foskett explores how to use mv-expand more precisely when working with arrays, evidence collections, repeated values and nested investigation data.

Why this mattersDynamic data and mv-expand work together. Once you can parse JSON, the next skill is expanding only the evidence that matters.

Final thought

Dynamic data is where much of the hidden investigation context lives.

Agent Foskett mindsetWhen a column looks messy, do not ignore it. Parse it, shape it and ask what evidence it contains.

Advanced KQL SeriesLesson 122 helps analysts turn JSON, arrays and nested fields into readable Microsoft Defender XDR evidence.

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

Advanced KQL Working with Dynamic Data

Agent Foskett Academy Lesson 122 teaches security analysts how to work with dynamic data, JSON objects, arrays and nested Microsoft Defender XDR telemetry using KQL.

Learn dynamic data in KQL for Microsoft Defender XDR

This lesson explains parse_json, todynamic, mv-expand, bag_unpack, property accessors and investigation-ready extraction patterns for Microsoft security telemetry.

Microsoft Defender XDR dynamic JSON investigation tutorial

Defenders can use KQL dynamic data techniques to extract evidence from AlertEvidence, CloudAppEvents, AuthenticationDetails and other nested telemetry fields.