Forum Discussion
In Advanced Hunting "Include in query" generates incorrect KQL for nested JSON array fields
Hi,
In Microsoft Defender Advanced Hunting, using the right-click "Include in query" option on a nested value inside SigninLogs.AuthenticationDetails generates incorrect KQL.
Example data structure:
[
{
"authenticationMethod": "Previously satisfied"
}
]
When I right-click the authenticationMethod value, Defender generates:
| where parse_json(AuthenticationDetails)["0.authenticationMethod"] == "Previously satisfied"
This returns no results.
The correct KQL is:
| where tostring(parse_json(AuthenticationDetails)[0].authenticationMethod) == "Previously satisfied"
or:
| where tostring(parse_json(AuthenticationDetails)[0]["authenticationMethod"]) == "Previously satisfied"
The generated syntax appears to treat "0.authenticationMethod" as one JSON property name instead of navigating to array element [0] and then accessing the authenticationMethod property.
I have also observed that Microsoft Sentinel generates the expected syntax for the same type of nested field, while the Microsoft Defender portal generates the incorrect form.
This is reproducible with SigninLogs.AuthenticationDetails, which is stored as a string containing a JSON array.
Expected behavior:
parse_json(AuthenticationDetails)[0].authenticationMethod
Actual behavior:
parse_json(AuthenticationDetails)["0.authenticationMethod"]
Plase confirm whether this is a known issue with the Advanced Hunting result / Inspect Record / Include in query functionality.