Dynamic json keys in AuditLogs

[endakelly]

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:

modifiedProperties

[

0 {"displayName":"WID","oldValue":null,"newValue":"\"\""},

1 {"displayName":"Active","oldValue":null,"newValue":"\"\""},

2 {"displayName":"WorkerID","oldValue":null,"newValue":"\"\""},

3 {"displayName":"UserID","oldValue":null,"newValue":"\"\""},

4 {"displayName":"BusinessTitle","oldValue":null,"newValue":"\"\""},

5 {"displayName":"Company","oldValue":null,"newValue":"\"\""},

6 {"displayName":"CountryReferenceFriendly","oldValue":null,"newValue":"\"\""},

7 {"displayName":"CountryReferenceNumeric","oldValue":null,"newValue":"\"\""},

8 {"displayName":"CountryReferenceTwoLetter","oldValue":null,"newValue":"\"\""},

9 {"displayName":"LocalReference","oldValue":null,"newValue":"\"\""},

10 {"displayName":"Municipality","oldValue":null,"newValue":"\"\""},

11 {"displayName":"PostalCode","oldValue":null,"newValue":"\"\""},

12 {"displayName":"PreferredFirstName","oldValue":null,"newValue":"\"\""},

13 {"displayName":"PreferredLastName","oldValue":null,"newValue":"\"\""},

14 {"displayName":"PreferredNameData","oldValue":null,"newValue":"\"\""},

15 {"displayName":"StatusHireDate","oldValue":null,"newValue":"\"\""},

16 {"displayName":"WorkerType","oldValue":null,"newValue":"\"\""},

17 {"displayName":"BusinessUnit","oldValue":null,"newValue":"\"\""},

18 {"displayName":"OfficeSite","oldValue":null,"newValue":"\"\""},

19 {"displayName":"ManagementLevel","oldValue":null,"newValue":"\"\""},

20 {"displayName":"CostCenter","oldValue":null,"newValue":"\"\""},

21 {"displayName":"FormattedAddress","oldValue":null,"newValue":"\"\""}

]

Say I want to get the newValue for the attribute UserID. I can do this with 

| extend UserIDfield=parse_json(parse_json(TargetResources)[0].modifiedProperties[3]).

The issue I encounter is that UserID is not always the third item, it could be any one of them and the number of attributes also changes so it's not in a predictable range.

Is there a way I can convert modifiedProperties into columns in a consistent way that is not hard coded?

[#2]

@endakelly 

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