TH2.8 Dynamic Column Parsing for M365 Logs

4-5 hours · Module 2 · Free

Operational Objective

M365 log tables pack critical hunting data inside nested JSON columns — LocationDetails, DeviceDetail, AdditionalData, RawEventData, ExtendedProperties, TargetResources. If you cannot extract the values from these nested structures, you cannot filter, aggregate, or correlate on them. This subsection teaches the parsing patterns used throughout the campaign modules.

Deliverable: Fluency with parse_json(), tostring(), dynamic field access, mv-expand, and bag_unpack for extracting hunting-relevant data from M365 nested columns.

⏱ Estimated completion: 25 minutes

The data is there. It is just nested.

The most valuable fields in M365 logs are the most deeply buried. The country of a sign-in is inside LocationDetails.countryOrRegion. The browser is inside DeviceDetail.browser. The ATT&CK techniques mapped to an alert are inside parse_json(ExtendedProperties).["Techniques"]. The application permissions consented in an audit event are inside TargetResources[0].modifiedProperties.

Every campaign module extracts from these nested structures. This subsection provides the extraction patterns you will use repeatedly.

Pattern 1: Simple dot-notation access

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// Extract location from SigninLogs
SigninLogs
| where TimeGenerated > ago(1d)
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend City = tostring(LocationDetails.city)
| extend Latitude = toreal(LocationDetails.geoCoordinates.latitude)
| extend Longitude = toreal(LocationDetails.geoCoordinates.longitude)
// tostring() is required because LocationDetails is a dynamic type
// Without tostring(), filters and aggregations may not work correctly
// Always cast to the expected type: tostring, toreal, toint, tobool

Pattern 2: Array access for TargetResources

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// Extract target details from AuditLogs
AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Consent to application"
| extend TargetApp = tostring(TargetResources[0].displayName)
| extend TargetAppId = tostring(TargetResources[0].id)
| extend TargetType = tostring(TargetResources[0].type)
// TargetResources is an array — [0] accesses the first element
// Some operations have multiple targets — check [1], [2] if needed
| extend ModifiedProps = tostring(
    TargetResources[0].modifiedProperties)
// ModifiedProps contains the permission details for consent events
// Further parse: parse_json(ModifiedProps) to access individual properties

Pattern 3: Extracting from modifiedProperties

The modifiedProperties field in AuditLogs is an array of objects, each with displayName, oldValue, and newValue. This is where consent permissions, role assignments, and policy changes are recorded.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
// Extract specific modified properties from audit events
AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName == "Consent to application"
| mv-expand ModProp = TargetResources[0].modifiedProperties
// mv-expand creates one row per modified property
| where ModProp.displayName == "ConsentAction.Permissions"
| extend Permissions = tostring(ModProp.newValue)
// Now Permissions contains the actual permission string
// Filter for high-privilege: where Permissions has "Mail.ReadWrite"
| project TimeGenerated,
    tostring(InitiatedBy.user.userPrincipalName),
    tostring(TargetResources[0].displayName),
    Permissions

Pattern 4: Parsing CloudAppEvents RawEventData

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
// Extract inbox rule details from CloudAppEvents
CloudAppEvents
| where TimeGenerated > ago(7d)
| where ActionType == "New-InboxRule"
| extend RawData = parse_json(RawEventData)
| extend RuleName = tostring(RawData.Parameters[?name=="Name"].Value)
| extend MoveToFolder = tostring(
    RawData.Parameters[?name=="MoveToFolder"].Value)
| extend SubjectContains = tostring(
    RawData.Parameters[?name=="SubjectContainsWords"].Value)
// Different events store parameters differently
// Use | project RawEventData | take 5 to examine the structure
//   before writing extraction logic
// RawEventData structures vary by ActionType — there is no universal schema

Pattern 5: bag_unpack for exploration

When you do not know the structure of a dynamic column, bag_unpack expands it into individual columns:

1
2
3
4
5
6
7
8
9
// Explore the structure of ExtendedProperties in SecurityAlert
SecurityAlert
| where TimeGenerated > ago(1d)
| take 1
| extend Properties = parse_json(ExtendedProperties)
| evaluate bag_unpack(Properties)
// Creates one column per key in ExtendedProperties
// Use this to discover what fields are available
// Then write targeted extraction queries for the fields you need

Figure TH2.8 — Five dynamic column parsing patterns. Use 1–2 for routine extraction, 3–4 for complex nested data, 5 for exploration of unfamiliar structures.

Try it yourself

Exercise: Extract consent permissions from your AuditLogs

Run Pattern 3 against your AuditLogs — extract the actual permission strings from consent events in the last 90 days. Which permissions appear most frequently? Do any events grant Mail.ReadWrite or Files.ReadWrite.All to non-admin users?

Then use bag_unpack (Pattern 5) on a CloudAppEvents RawEventData field for a "New-InboxRule" event. Examine the structure. Which parameters record the rule name, conditions, and actions?

⚠ Compliance Myth: "Dynamic columns are unreliable — only use standard columns for hunting"

The myth: The nested JSON in dynamic columns is unstructured and unreliable. Hunting should use only the top-level typed columns.

The reality: The top-level columns (TimeGenerated, UserPrincipalName, IPAddress, ResultType) answer “what happened.” The dynamic columns (LocationDetails, DeviceDetail, RawEventData, TargetResources) answer “how, where, and with what context.” Hunting requires both. The permission scope of a consented application is only in TargetResources.modifiedProperties. The inbox rule conditions are only in RawEventData. The country of a sign-in is only in LocationDetails. Ignoring dynamic columns means ignoring the contextual data that makes the difference between an indicator and a finding.

Extend this approach

When a dynamic column's structure varies by event type (common with RawEventData in CloudAppEvents), use the exploration pattern first: `| where ActionType == "YourEvent" | take 5 | project RawEventData`. Examine the raw JSON. Then write the targeted extraction query. Do not assume the structure matches a different ActionType's format — CloudAppEvents uses different RawEventData schemas for different operations. TH5 (inbox rules) and TH6 (OAuth) each document the specific extraction patterns for their relevant ActionTypes.

References Used in This Subsection

Microsoft. “KQL Dynamic Data Type.” Microsoft Learn. https://learn.microsoft.com/en-us/kusto/query/scalar-data-types/dynamic
Microsoft. “KQL mv-expand Operator.” Microsoft Learn. https://learn.microsoft.com/en-us/kusto/query/mv-expand-operator

You're reading the free modules of this course

The full course continues with advanced topics, production detection rules, worked investigation scenarios, and deployable artifacts. Premium subscribers get access to all courses.

View Pricing See Full Syllabus

← TH2.7 arg_max and arg_min for Entity Investigation TH2.9 materialize() for Query Performance →