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

Advanced KQL: Advanced parse_json().

The evidence was there.

It just was not sitting in a normal column.

Authentication methods. Alert entities. Device details. Cloud metadata. Policy outcomes.

To most analysts, it looked like unreadable text. Agent Foskett knew it was structured JSON waiting to be explored. Sometimes the most valuable evidence is not missing. It is simply hidden one level deeper.

Start Lesson 126 Back to Academy
Agent Foskett Academy lesson using advanced parse_json in Microsoft Defender XDR
Lesson overview

Learn how to parse nested JSON, extract dynamic properties, expand arrays and turn complex Microsoft Defender XDR telemetry into readable investigation columns.

Parse complex dynamic objects
Navigate nested JSON properties
Combine parse_json() with mv-expand
Build readable investigation fields

Why advanced parse_json() matters

Microsoft Defender XDR often stores rich investigation evidence inside dynamic fields. Advanced parse_json() techniques help analysts expose that hidden structure without losing context.
Defender telemetry is richImportant evidence may be stored as JSON inside fields such as AdditionalFields, AuthenticationDetails, Entities and RawEventData.
One column can hold many cluesA single dynamic object can contain authentication methods, device posture, policy results, app metadata, alert entities and nested arrays.
Parsing turns noise into evidenceparse_json() helps transform complex dynamic content into readable columns that can be filtered, summarised, joined and reviewed.

The advanced JSON mindset

Do not treat JSON as messy text. Treat it as structured evidence with properties, objects and arrays that can be navigated.
Parse onceCreate one parsed object with extend, then reuse it throughout the query instead of calling parse_json() repeatedly.
Navigate deliberatelyUse dot notation for simple properties and bracket notation when property names contain spaces, punctuation or unusual characters.
Convert output typesUse tostring(), toint(), todatetime() and todynamic() when values need to be compared, sorted, joined or displayed clearly.
Handle missing valuesNot every event has every JSON property. Defensive queries check for nulls and provide clear fallback values.
Expand arrays only when neededArrays can multiply rows quickly. Use mv-expand carefully and only after filtering to the events that matter.
Build investigation columnsThe goal is not just to parse JSON. The goal is to expose useful evidence for the analyst.

Step 1 — Parse a dynamic field once

Start by converting a JSON-like field into a dynamic object that can be reused later in the query.
step-1-parse-once.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
let TimeFrame = 7d;
DeviceEvents
| where Timestamp > ago(TimeFrame)
| where isnotempty(AdditionalFields)
| extend ParsedFields = parse_json(AdditionalFields)
| project Timestamp,
          DeviceName,
          ActionType,
          ParsedFields
| take 50

Step 2 — Access individual JSON properties

Once the field is parsed, use dot notation to extract individual properties into readable columns.
step-2-access-properties.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
let TimeFrame = 7d;
DeviceEvents
| where Timestamp > ago(TimeFrame)
| where isnotempty(AdditionalFields)
| extend ParsedFields = parse_json(AdditionalFields)
| extend EvidenceType = tostring(ParsedFields.EvidenceType),
         DetectionSource = tostring(ParsedFields.DetectionSource),
         PolicyName = tostring(ParsedFields.PolicyName)
| project Timestamp,
          DeviceName,
          ActionType,
          EvidenceType,
          DetectionSource,
          PolicyName

Step 3 — Navigate nested JSON objects

Nested objects require walking through each level of the JSON structure.
step-3-nested-objects.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
let TimeFrame = 7d;
CloudAppEvents
| where Timestamp > ago(TimeFrame)
| where isnotempty(RawEventData)
| extend Raw = parse_json(RawEventData)
| extend UserAgent = tostring(Raw.UserAgent),
         AppDisplayName = tostring(Raw.Application.DisplayName),
         ResourceName = tostring(Raw.TargetResource.DisplayName)
| project Timestamp,
          AccountDisplayName,
          ActionType,
          Application,
          AppDisplayName,
          ResourceName,
          UserAgent

Step 4 — Use bracket notation for awkward property names

When JSON property names contain spaces, hyphens or special characters, bracket notation is safer than dot notation.
step-4-bracket-notation.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
let TimeFrame = 7d;
EmailEvents
| where Timestamp > ago(TimeFrame)
| where isnotempty(AuthenticationDetails)
| extend Auth = parse_json(AuthenticationDetails)
| extend SPF = tostring(Auth["SPF"]),
         DKIM = tostring(Auth["DKIM"]),
         DMARC = tostring(Auth["DMARC"]),
         CompositeAuth = tostring(Auth["Composite Authentication"])
| project Timestamp,
          SenderFromAddress,
          RecipientEmailAddress,
          SPF,
          DKIM,
          DMARC,
          CompositeAuth

Step 5 — Convert parsed values into useful types

Parsed JSON values often need type conversion before they can be filtered, sorted or summarised reliably.
step-5-convert-values.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
let TimeFrame = 14d;
DeviceEvents
| where Timestamp > ago(TimeFrame)
| extend ParsedFields = parse_json(AdditionalFields)
| extend Score = toint(ParsedFields.RiskScore),
         FirstSeen = todatetime(ParsedFields.FirstSeen),
         Indicator = tostring(ParsedFields.Indicator)
| where Score >= 50
| project Timestamp,
          DeviceName,
          ActionType,
          Indicator,
          Score,
          FirstSeen
| order by Score desc

Step 6 — Handle missing properties safely

Advanced JSON queries should expect missing properties and provide a readable fallback.
step-6-handle-missing-properties.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
let TimeFrame = 7d;
CloudAppEvents
| where Timestamp > ago(TimeFrame)
| extend Raw = parse_json(RawEventData)
| extend Country = tostring(Raw.Location.Country),
         City = tostring(Raw.Location.City),
         LocationSummary = iff(isempty(Country), "Unknown location", strcat(City, ", ", Country))
| project Timestamp,
          AccountDisplayName,
          ActionType,
          Application,
          IPAddress,
          LocationSummary

Step 7 — Expand arrays after parsing

When JSON contains an array of evidence items, parse the parent object first and then expand the array.
step-7-expand-json-arrays.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
let TimeFrame = 7d;
AlertEvidence
| where Timestamp > ago(TimeFrame)
| where isnotempty(AdditionalFields)
| extend ParsedFields = parse_json(AdditionalFields)
| extend EvidenceItems = todynamic(ParsedFields.Evidence)
| mv-expand EvidenceItems
| extend EvidenceType = tostring(EvidenceItems.Type),
         EvidenceName = tostring(EvidenceItems.Name),
         EvidenceValue = tostring(EvidenceItems.Value)
| project Timestamp,
          AlertId,
          EvidenceType,
          EvidenceName,
          EvidenceValue

Step 8 — Parse AuthenticationDetails for email investigations

Email authentication fields often contain compact JSON-like evidence that becomes clearer when extracted into separate columns.
step-8-authenticationdetails.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 TimeFrame = 30d;
EmailEvents
| where Timestamp > ago(TimeFrame)
| where isnotempty(AuthenticationDetails)
| extend Auth = parse_json(AuthenticationDetails)
| extend SPF = tostring(Auth.SPF),
         DKIM = tostring(Auth.DKIM),
         DMARC = tostring(Auth.DMARC),
         CompAuth = tostring(Auth.CompositeAuthentication)
| where DMARC has "fail" or SPF has "fail" or DKIM has "fail"
| project Timestamp,
          SenderFromAddress,
          SenderMailFromAddress,
          RecipientEmailAddress,
          Subject,
          SPF,
          DKIM,
          DMARC,
          CompAuth,
          NetworkMessageId

Step 9 — Build readable investigation columns

The final output should help an analyst understand the event without opening the raw JSON repeatedly.
step-9-readable-columns.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
let TimeFrame = 7d;
DeviceEvents
| where Timestamp > ago(TimeFrame)
| where isnotempty(AdditionalFields)
| extend ParsedFields = parse_json(AdditionalFields)
| extend InvestigationSource = tostring(ParsedFields.Source),
         RuleName = tostring(ParsedFields.RuleName),
         ThreatName = tostring(ParsedFields.ThreatName),
         RemediationStatus = tostring(ParsedFields.RemediationStatus)
| project Timestamp,
          DeviceName,
          ActionType,
          InvestigationSource,
          RuleName,
          ThreatName,
          RemediationStatus
| order by Timestamp desc

Step 10 — Wrap JSON parsing into reusable logic

Advanced parsing patterns become even more powerful when they are wrapped into reusable functions.
step-10-reusable-json-parser.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
let ParsedDeviceEvidence = (Lookback:timespan) {
    DeviceEvents
    | where Timestamp > ago(Lookback)
    | where isnotempty(AdditionalFields)
    | extend ParsedFields = parse_json(AdditionalFields)
    | extend EvidenceSource = tostring(ParsedFields.Source),
             EvidenceRule = tostring(ParsedFields.RuleName),
             EvidenceStatus = tostring(ParsedFields.Status)
    | project Timestamp,
              DeviceName,
              ActionType,
              EvidenceSource,
              EvidenceRule,
              EvidenceStatus
};
ParsedDeviceEvidence(14d)
| summarize Events = count(), FirstSeen = min(Timestamp), LastSeen = max(Timestamp) by DeviceName, EvidenceRule
| order by Events desc

Common parse_json() mistakes

Most parsing problems come from treating dynamic data like plain text, or from expanding and converting values too late.
Parsing repeatedlyCalling parse_json() again and again makes queries harder to read and less efficient. Parse once and reuse the parsed object.
Forgetting type conversionDynamic values often need tostring(), toint() or todatetime() before they behave as expected in filters and summaries.
Assuming every property existsSome events have missing or differently shaped JSON. Use isempty(), isnotempty() and fallback values where needed.
Expanding too earlymv-expand can multiply rows dramatically. Filter first, then expand only the arrays you actually need.
Ignoring nested structureUseful evidence may sit several layers down. Check the shape of the JSON before deciding the evidence is not available.
Showing raw JSON onlyRaw JSON is useful during exploration, but final investigation output should expose clean columns for analysts.

Investigation checklist

Use this checklist before closing a JSON-heavy Defender XDR investigation.
Is the field really JSON?Confirm whether the field contains structured JSON, a dynamic object, an array or plain text before choosing the parsing method.
Did I parse once?Create one parsed object and reference it throughout the query to keep the logic clean and efficient.
Are properties nested?Check whether the value is at the top level or inside a nested object such as Location, Application, Device or TargetResource.
Does it contain arrays?If the field contains multiple entities, evidence items or authentication entries, use mv-expand carefully after filtering.
Have I converted types?Convert dynamic values into strings, integers or datetimes before filtering, summarising or joining.
Is the output analyst-friendly?The final table should show readable evidence columns, not force the analyst to inspect raw JSON manually.

Related Agent Foskett Academy lessons

These lessons support deeper JSON parsing and dynamic Microsoft Defender XDR investigations.
Lesson 122 — Advanced KQL: Working with Dynamic DataUnderstand the foundations of dynamic fields before parsing complex JSON structures.
Lesson 123 — Advanced KQL: Mastering mv-expandExpand arrays and multi-value fields after parsing JSON objects.
Lesson 124 — Advanced KQL: Optimising Query PerformanceKeep JSON parsing efficient by filtering early and avoiding repeated expensive operations.
Lesson 125 — Advanced KQL: Building Reusable KQL FunctionsWrap trusted parsing logic into reusable functions for repeatable investigations.
Lesson 127 — Advanced KQL: Advanced Regular ExpressionsThe next lesson moves from structured JSON parsing into advanced pattern matching for unstructured text.
Lesson 129 — Building Enterprise Hunting QueriesUse JSON parsing patterns as part of larger enterprise hunting workflows.

Coming next

The Advanced KQL series continues by moving from structured JSON into advanced pattern matching.
Lesson 127 — Advanced KQL: Advanced Regular ExpressionsNext, Agent Foskett explores regex patterns, precise extraction, suspicious command-line matching and advanced text analysis in Microsoft Defender XDR.
Why this mattersNot every clue is stored as clean JSON. Some evidence is buried inside command lines, URLs, file paths, subjects and raw text fields.

Final thought

Modern Defender XDR investigations are not limited by the data you collect. They are limited by how deeply you can understand it.
Agent Foskett mindsetDo not stop at the first layer. Parse the object, inspect the structure, expand the arrays and turn hidden evidence into readable columns.
Advanced KQL SeriesLesson 126 teaches analysts how to reveal the structured evidence hidden inside dynamic Microsoft Defender XDR telemetry.
Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD

Advanced KQL Advanced parse_json in Microsoft Defender XDR

Agent Foskett Academy Lesson 126 teaches defenders how to use parse_json() for advanced KQL investigations in Microsoft Defender XDR, including nested JSON objects, arrays, dynamic fields and readable investigation columns.

Learn parse_json for Microsoft security investigations

Advanced parse_json() techniques help Microsoft security analysts extract hidden evidence from AdditionalFields, AuthenticationDetails, RawEventData, AlertEvidence and other dynamic Defender XDR telemetry fields.

Microsoft Defender XDR JSON parsing tutorial

This lesson explains how to parse JSON once, access nested properties, use bracket notation, convert values, handle missing fields, expand arrays with mv-expand and build reusable parsing logic for threat hunting.

ACSC Logo

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT 2026