topic Re: Json match `field.name` to `field.value` fields in Getting Data In

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

oerd_rbal — Tue, 18 Oct 2016 12:51:48 GMT

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!

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

sundareshr — Tue, 18 Oct 2016 13:11:40 GMT

Try this

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

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

oerd_rbal — Tue, 18 Oct 2016 13:51:29 GMT

@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?

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

sundareshr — Tue, 18 Oct 2016 14:08:47 GMT

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

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

mattlorimor — Thu, 10 Nov 2016 19:09:32 GMT

@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.

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

oerd_rbal — Fri, 11 Nov 2016 13:05:18 GMT

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

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

jschroth — Fri, 27 Jan 2017 05:51:25 GMT

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

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

DalJeanis — Sat, 28 Jan 2017 00:39:59 GMT

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.

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

oerd_rbal — Tue, 21 Feb 2017 16:10:40 GMT

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