Agent Foskett Academy • Lesson 93 • Advanced KQL

Working with Dynamic Data and JSON in KQL.

The evidence was not missing.

It was hidden inside a JSON object.

One field contained dozens of values.

Agent Foskett knew the investigation depended on extracting the right one.

Start Lesson 93 Back to Academy

Agent Foskett Academy lesson working with dynamic data and JSON in KQL

Lesson overview

Learn how to extract useful evidence from dynamic fields and JSON objects in Microsoft Defender XDR using KQL functions such as parse_json(), todynamic(), tostring(), toint() and mv-expand.

Understand dynamic data

Parse JSON objects safely

Extract nested properties

Expand arrays during investigations

Why dynamic data matters

Microsoft Defender XDR telemetry often stores rich evidence inside dynamic columns. If defenders only read the visible columns, they may miss the details that explain what really happened.

One field can hold many valuesDynamic fields can contain objects, arrays, nested properties and investigation context that would be difficult to store in normal columns.

JSON hides useful evidenceFields such as AdditionalFields may include process details, risk values, policy outcomes, device status or other important investigation clues.

Parsing makes evidence readableKQL functions such as parse_json(), tostring() and mv-expand help turn complex dynamic data into clear, usable investigation columns.

The dynamic data investigation workflow

Agent Foskett follows a simple process: find the dynamic field, parse it, extract the useful properties, convert values and then validate the evidence.

1. Find the dynamic columnIdentify fields such as AdditionalFields or other columns that contain JSON-like evidence.

2. Parse the objectUse parse_json() or todynamic() to convert the raw value into a dynamic object that KQL can query.

3. Extract propertiesUse dot notation or bracket notation to pull out specific fields that matter to the investigation.

4. Convert data typesUse tostring(), toint(), todatetime() or tobool() so extracted values behave correctly in filters and summaries.

5. Expand arraysUse mv-expand when one record contains multiple values that need to be analysed separately.

6. Project clean evidenceKeep the final output readable by projecting only the timestamp, entity fields and extracted evidence.

Step 1 — Inspect the dynamic field

Start by looking at the raw dynamic value so you understand what properties are available before extracting anything.

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

Step 2 — Parse JSON with parse_json()

Use parse_json() to convert a JSON string into a dynamic object that can be queried directly.

DeviceEvents
| where Timestamp > ago(24h)
| where isnotempty(AdditionalFields)
| extend Details = parse_json(AdditionalFields)
| project Timestamp,
          DeviceName,
          ActionType,
          Details

Step 3 — Extract specific properties

Once the object is parsed, extract the fields that are useful for the investigation and convert them into normal values.

DeviceEvents
| where Timestamp > ago(24h)
| extend Details = parse_json(AdditionalFields)
| extend PolicyName = tostring(Details.PolicyName)
| extend RiskScore = toint(Details.RiskScore)
| project Timestamp,
          DeviceName,
          ActionType,
          PolicyName,
          RiskScore
| order by Timestamp desc

Step 4 — Use bracket notation for difficult property names

Some JSON property names contain spaces, dots or characters that do not work cleanly with dot notation. Bracket notation helps extract them safely.

DeviceEvents
| where Timestamp > ago(24h)
| extend Details = parse_json(AdditionalFields)
| extend DetectionName = tostring(Details["Detection Name"])
| extend ThreatCategory = tostring(Details["Threat.Category"])
| project Timestamp,
          DeviceName,
          ActionType,
          DetectionName,
          ThreatCategory

Step 5 — Expand arrays with mv-expand

When a dynamic field contains an array, use mv-expand to turn each array item into its own row for investigation.

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

Step 6 — Build a cleaner investigation output

The goal is not to show the whole JSON object. The goal is to extract the fields that make the investigation easier to read and explain.

DeviceEvents
| where Timestamp > ago(7d)
| where isnotempty(AdditionalFields)
| extend Details = parse_json(AdditionalFields)
| extend ExtractedUser = tostring(Details.UserName)
| extend ExtractedIp = tostring(Details.IpAddress)
| extend ExtractedProcess = tostring(Details.ProcessName)
| extend ExtractedResult = tostring(Details.Result)
| project Timestamp,
          DeviceName,
          ActionType,
          ExtractedUser,
          ExtractedIp,
          ExtractedProcess,
          ExtractedResult
| order by Timestamp desc

How to read dynamic evidence

Dynamic data becomes powerful when defenders treat it as hidden evidence rather than messy text.

Raw JSON is not the answerA large JSON blob may contain the evidence, but it is difficult to read, filter or explain during an incident.

Extraction creates clarityTurning JSON properties into normal columns makes it easier to compare users, devices, actions and outcomes.

Type conversion mattersA value extracted from JSON may look like a number or date, but it often needs to be converted before filtering or summarising.

Arrays need expansionIf one event contains multiple users, indicators or actions, mv-expand can help separate the evidence into individual rows.

Real-world investigation

The confusing eventAn alert references a suspicious device event, but the visible columns do not explain why the action mattered.

The hidden detailsAgent Foskett opens AdditionalFields and finds a JSON object containing policy names, user context, process details and risk values.

The extractionUsing parse_json(), tostring() and toint(), he extracts the values into clean investigation columns.

The arrayOne property contains several indicators. mv-expand separates each indicator so it can be hunted across the environment.

The resultThe final query produces a readable table showing the device, user, process, risk score and indicator values involved in the suspicious activity.

The conclusionThe evidence was always present. It only became useful once the dynamic data was parsed, converted and projected clearly.

Investigation checklist

Identify dynamic fieldsLook for columns that contain JSON objects, arrays or nested evidence.

Parse before extractingUse parse_json() or todynamic() before trying to access properties.

Use dot notation carefullyUse dot notation for simple property names and bracket notation for difficult names.

Convert extracted valuesUse tostring(), toint(), todatetime() or tobool() so the values behave correctly.

Expand arrays when neededUse mv-expand when a single event contains multiple values that need separate rows.

Keep the final table readableProject the extracted columns that help explain the incident and remove noisy raw JSON where possible.

Related Agent Foskett Academy lessons

Parsing JSON with parse_json()Review the earlier Academy lesson that introduced parsing JSON in KQL.

Expanding Multi-Value Data with mv-expandLearn how to expand arrays and multi-value fields into separate rows.

Advanced Multi-Value Investigations with mv-applyExplore more controlled multi-value investigations using mv-apply.

Using let Statements to Build Reusable Hunting QueriesCreate reusable variables and simplify complex KQL investigations.

Advanced join Techniques in KQLCorrelate evidence across multiple Defender XDR tables using advanced joins.

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

Coming next

Lesson 94 — Optimising KQL Queries for PerformanceNext, Agent Foskett Academy explains how defenders write faster and more efficient KQL queries by reducing data early, choosing useful filters and avoiding expensive query patterns.

Why this mattersParsing JSON is powerful, but advanced hunting queries still need to run efficiently, especially when analysing large Microsoft Defender XDR tables.

Final thought

Dynamic data is not noise. It is structured evidence waiting to be extracted.

Agent Foskett mindsetDo not ignore a field just because it looks messy. Parse it, inspect it and extract the evidence that matters.

The hidden field told the storyOnce the JSON was parsed, the investigation changed from confusing to explainable.

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

Working with Dynamic Data and JSON in KQL

Agent Foskett Academy Lesson 93 teaches defenders how to work with dynamic data and JSON in KQL for Microsoft Defender XDR threat hunting.

Dynamic data and JSON in Microsoft Defender XDR

This lesson explains parse_json(), todynamic(), dot notation, bracket notation, tostring(), toint(), mv-expand, AdditionalFields and practical ways to extract hidden evidence from Microsoft Defender XDR telemetry.