Json match `field.name` to `field.value` fields

[oerd_rbal]

Hi all,

I have some office 365 json events that have an ExtendedProperties array field containing multiple json objects, as in the example below:

{ "ExtendedProperties" : [
        {   "Name" : Role.ObjectId ,
            "Value" : xxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx5e10
        },
        {
            "Name" : Role.RoleTemplateId ,
            "Value" : xxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx5e10
        },
        {   "Name" : Role.DisplayName,
            "Value" : Company Administrator
        },
        {   "Name" : Role.WellKnownObject,
            "Value" : TenantAdmins
        }
    ]
}

I am interested in filtering finding Value where Name=Role.DisplayName. It would all be a lot easier if there were a way to create new fields where Role.DisplayName = "Company Administrator".

Thanks!

[jschroth · Accepted solution]

It's a bit odd, but I found splitting the values apart and putting them back together works:

 eval names='ExtendedProperties{}.Name', vals='ExtendedProperties{}.Value' | eval temp=mvzip(names,vals) | mvexpand temp | rex field=temp "(?<name>[^,]+),(?<val>[^,]+)" | eval {name}=val | transaction Id

---

updated to add back the deleted terms in angle brackets. - dmj

[DalJeanis]

I find it safer to use four or five exclamation points "!!!!" as my delimiter for mvzip, rather than a comma, since it almost never appears in my data and commas often do.

Your code would look something like this -

  eval names='ExtendedProperties{}.Name', vals='ExtendedProperties{}.Value' | eval temp=mvzip(names,vals,"!!!!") | mvexpand temp | rex field=temp "(?<name>..*?)!!!!(?<val>.*)" | eval {name}=val | transaction Id

By the way, you have to mark your code as code before the first time you submit it, or the angle-brackets get deleted by the web interface. I've put the field names back into the regex.

[oerd_rbal]

Thanks, man! Such a pity the angular brackets were removed by code formatting...

[sundareshr]

Try this

.... | eval {Name}=Value | search "Role.DisplayName"="Company Administrator"

[oerd_rbal]

@sundareshr Name is actually contained in ExtendedProperties and | eval {ExtendedProperties.Name}=ExtendedProperties.Value ... doesn't create an Role.DisplayName field.
Is there a way to do this with foreach?

[mattlorimor]

@oerd_rbal - Did you ever get this to work? I'm working with almost an identical JSON structure. Nothing on this page has worked for me to automatically extract the fields.

[oerd_rbal]

@mattlorimor No, I gave up altogether and changed my approach to the much less elegant one of extracting fields based on regexes... 😞

[sundareshr]

Try enclosing fieldnames within single-quotes. | eval {'ExtendedProperties.Name'}='ExtendedProperties.Value' | ...