I'm attempting to parse the FrontDoorWebApplicationFirewallLog which has an AdditionalFields column as described below. I thought I could run mv-expand and then evaluate bag_unpack to parse this column. However, mv-expand has no effect on the details_matches column, it simply returns the original input. What I would like it to do is to break each JSON string into a separate row. How to achieve that?
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
edit: Question has now been answered by David. Would still like to know why
| extend Column_d = todynamic(Column)
| extend details_matches = Column_d.details_matches
| mv-expand details_matches
does not have any effect on the column details_matches, while the answer below does. Why is that?
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 = parse_json(Column)
| evaluate bag_unpack(Column)
| mv-expand parse_json(details_matches)
| evaluate bag_unpack(details_matches)
| mv-expand parse_json(matchVariableValue)
| evaluate bag_unpack(matchVariableValue)
| details_msg | socketIP | matchVariableName | folderId | folderName | isSubFolderForMcApp | ownerId | referrer | searchLevel | time | url |
|---|---|---|---|---|---|---|---|---|---|---|
| Detects MySQL comment-/space-obfuscated injections and backtick termination | 1.1.1.6 | CookieValue:search.settings.breadcrumb | Saved Settings | false | 0 | |||||
| Detects MySQL comment-/space-obfuscated injections and backtick termination | 1.1.1.6 | CookieValue:search.settings.breadcrumb | 29193 | My saved settings | false | 60409 | 0 | |||
| Detects MySQL comment-/space-obfuscated injections and backtick termination | 1.1.1.6 | CookieValue:user.trail | Last page | Tue Nov 29 2022 07:09:00 GMT+0100 (Central European Standard Time) | pletion | |||||
| Detects MySQL comment-/space-obfuscated injections and backtick termination | 1.1.1.6 | CookieValue:user.trail | Last page | Tue Nov 29 2022 07:09:04 GMT+0100 (Central European Standard Time) | Completion | |||||
| Detects MySQL comment-/space-obfuscated injections and backtick termination | 1.1.1.6 | CookieValue:user.trail | Last page | Tue Nov 29 2022 08:19:51 GMT+0100 (Central European Standard Time) | rojectslixx | |||||
| Detects MySQL comment-/space-obfuscated injections and backtick termination | 1.1.1.6 | CookieValue:user.trail | Last page | Tue Nov 29 2022 08:19:59 GMT+0100 (Central European Standard Time) | 67 | |||||
| Detects MySQL comment-/space-obfuscated injections and backtick termination | 1.1.1.6 | CookieValue:user.trail | Last page | Tue Nov 29 2022 08:20:04 GMT+0100 (Central European Standard Time) | 913384 |