解析FrontDoorWAF日志动态JSON数组：mv-expand无效原因及解决

问题背景 #

我尝试解析FrontDoorWebApplicationFirewallLog的AdditionalFields列，原本想用mv-expand配合evaluate bag_unpack解析，但mv-expand对details_matches列完全无效，只会返回原始输入。需求是把该列中的每个JSON对象拆分成单独行。

测试代码如下：

datatable(Column: string) [ 
   '{"socketIP":"1.1.1.6","details_matches":"[\r\n  {\r\n    \"matchVariableName\": \"CookieValue:search.settings.breadcrumb\",\r\n    \"matchVariableValue\": \"[{\\\\\"ownerId\\\\\":null,\\\\\"folderId\\\\\":null,\\\\\"folderName\\\\\":\\\\\"Saved Settings\\\\\",\\\\\"searchLevel\\\\\":0,\\\\\"isSubFolderForMcApp\\\\\":false},{\\\\\"ownerId\\\\\":60409,\\\\\"folderId\\\\\":29193,\\\\\"folderName\\\\\":\\\\\"My saved settings\\\\\",\\\\\"searchLevel\\\\\":0,\\\\\"isSubFolderForMcApp\\\\\":false}]\"\r\n  },\r\n  {\r\n    \"matchVariableName\": \"CookieValue:user.trail\",\r\n    \"matchVariableValue\": \"[{\\\\\"url\\\\\":\\\\\"pletion\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 07:09:00 GMT+0100 (Central European Standard Time)\\\\\"},{\\\\\"url\\\\\":\\\\\"Completion\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 07:09:04 GMT+0100 (Central European Standard Time)\\\\\"},{\\\\\"url\\\\\":\\\\\"rojectslixx\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 08:19:51 GMT+0100 (Central European Standard Time)\\\\\"},{\\\\\"url\\\\\":\\\\\"67\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 08:19:59 GMT+0100 (Central European Standard Time)\\\\\"},{\\\\\"url\\\\\":\\\\\"913384\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 08:20:04 GMT+0100 (Central European Standard Time)\\\\\"}]\"\r\n  }\r\n]","details_msg":"Detects MySQL comment-/space-obfuscated injections and backtick termination"}'
]
| extend Column_d = todynamic(Column)
| extend socketIp_s = Column_d.socketIP
| extend details_msg = Column_d.details_msg
| extend details_matches = Column_d.details_matches
| project socketIp_s, details_matches, details_msg, Column_d

疑问：为何这段代码无效？ #

问题已解决，但想明白为什么以下代码无法拆分details_matches列：

| extend Column_d = todynamic(Column)
| extend details_matches = Column_d.details_matches
| mv-expand details_matches 

原因分析 #

核心问题是**details_matches的类型不对**：

• 执行todynamic(Column)后，Column_d.details_matches仍然是字符串类型——因为原始日志里的details_matches是被双引号包裹的序列化JSON数组（相当于JSON里嵌套了一层字符串化的JSON）。
• mv-expand仅对动态数组/动态对象类型的列生效，对字符串类型列直接返回原内容，不会做任何拆分。

正确解决方案 #

需要先把details_matches字符串再次转换为动态类型，再执行mv-expand，完整代码如下：

datatable(Column: string) [ 
   '{"socketIP":"1.1.1.6","details_matches":"[\r\n  {\r\n    \"matchVariableName\": \"CookieValue:search.settings.breadcrumb\",\r\n    \"matchVariableValue\": \"[{\\\\\"ownerId\\\\\":null,\\\\\"folderId\\\\\":null,\\\\\"folderName\\\\\":\\\\\"Saved Settings\\\\\",\\\\\"searchLevel\\\\\":0,\\\\\"isSubFolderForMcApp\\\\\":false},{\\\\\"ownerId\\\\\":60409,\\\\\"folderId\\\\\":29193,\\\\\"folderName\\\\\":\\\\\"My saved settings\\\\\",\\\\\"searchLevel\\\\\":0,\\\\\"isSubFolderForMcApp\\\\\":false}]\"\r\n  },\r\n  {\r\n    \"matchVariableName\": \"CookieValue:user.trail\",\r\n    \"matchVariableValue\": \"[{\\\\\"url\\\\\":\\\\\"pletion\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 07:09:00 GMT+0100 (Central European Standard Time)\\\\\"},{\\\\\"url\\\\\":\\\\\"Completion\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 07:09:04 GMT+0100 (Central European Standard Time)\\\\\"},{\\\\\"url\\\\\":\\\\\"rojectslixx\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 08:19:51 GMT+0100 (Central European Standard Time)\\\\\"},{\\\\\"url\\\\\":\\\\\"67\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 08:19:59 GMT+0100 (Central European Standard Time)\\\\\"},{\\\\\"url\\\\\":\\\\\"913384\\\\\",\\\\\"referrer\\\\\":\\\\\"Last page\\\\\",\\\\\"time\\\\\":\\\\\"Tue Nov 29 2022 08:20:04 GMT+0100 (Central European Standard Time)\\\\\"}]\"\r\n  }\r\n]","details_msg":"Detects MySQL comment-/space-obfuscated injections and backtick termination"}'
]
| extend Column_d = todynamic(Column)
| extend socketIp_s = Column_d.socketIP
| extend details_msg = Column_d.details_msg
// 关键步骤：将字符串类型的details_matches转换为动态数组
| extend details_matches = todynamic(Column_d.details_matches)
// 现在可以正常拆分数组为单独行
| mv-expand details_matches
// 可选：拆分包对象为单独列
| evaluate bag_unpack(details_matches)

代码说明 #

1. 第二次调用todynamic(Column_d.details_matches)：把嵌套的字符串化JSON数组转换为Kusto的动态数组类型。
2. mv-expand details_matches：将动态数组中的每个对象拆分为单独行。
3. evaluate bag_unpack(details_matches)：把每个动态对象的键值对展开为单独列（可选步骤，根据需求决定是否使用）。

内容的提问来源于stack exchange，提问作者dueland