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!
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
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
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.
Thanks, man! Such a pity the angular brackets were removed by code formatting...
Try this
.... | eval {Name}=Value | search "Role.DisplayName"="Company Administrator"
@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
?
@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.
@mattlorimor No, I gave up altogether and changed my approach to the much less elegant one of extracting fields based on regexes... 😞
Try enclosing fieldnames within single-quotes. | eval {'ExtendedProperties.Name'}='ExtendedProperties.Value' | ...