Lookup display_name from an id in nested json

[stephencrim]

I have some JSON events coming in via the HTTP Event collector. One of the elements within it has an 'owner_id', which references another element in the event called 'users' which has multiple child elements containing 'id' and 'display_name'. I'm trying to eval a new field 'owner_display_name' for each event matching the 'display_name' whose 'id' matches 'owner_id'.

Sample JSON:

{   
     deal:  {   
        }   
         name:  test    
         owner_id:   2000066958 
    }
     users: [   
        {
         display_name:   John Doe
         email:  jdoe@foobar.com    
         id:     2000066958 
         is_active:  true   
         mobile_number:  null   
         work_number:    null   
        }   
    ]   
}

I've been trying to use spath and various mv functions, but none of them seem quite as straightforward as I would think this should be. What I'm essentially looking for is an inline lookup that uses an mv field within the event to eval a new field based on some conditional logic. I've been able to grab the first element from the users field, and that is pretty close, but assumes that the owner is the first user in the list, and there's no guarantee that will always be the case.

[stephencrim · Accepted solution]

Our temporary resolution to this was to simply do the lookup in python prior to sending the json to Splunk.

What we're looking at longer term is to create some lookup kvstores and populate those periodically from a splunk search on the same event data. This will give us the lookup tables for the data we need to evaluate at search time and not have to do all of this preprocessing every time we decide we want a new field populated for a report.