Mar 26 2020
08:05 AM
- last edited on
Apr 08 2022
10:21 AM
by
TechCommunityAP
Mar 26 2020
08:05 AM
- last edited on
Apr 08 2022
10:21 AM
by
TechCommunityAP
I'm trying to get consistent columns from some AuditLogs. The problem is that the json key values are dynamic and change constantly and even the number of json keys can vary.
The data I want to extract is in an array called modifiedProperties which is inside another array called TargetResources.
ModifiedProperties always has a json key value of 1 which is fine but the items inside have varying json key values. Here is some scrubbed sample output:
Mar 27 2020 06:25 AM
I have used variants of this query for these. I didn't have any "UserID" data so used "Tech Reads" to test. You can certainly massively improve this query, but it shows a technique.
let srch = "displayName";
search in (AuditLogs) srch
| evaluate narrow()
| where Value contains srch
| where Column == "TargetResources"
| parse Value with * '[{"displayName":"' displayName '","modifiedProperties"' *
| where displayName == "Tech Reads"
| parse Value with * '"newValue":"[\\"' newValue '\\"]"},' *
| where isnotempty(newValue)
Thanks Clive