Getting Data In

How do you extract information from an array of key values in a column with multiple keys?

chalbersma
Engager

So I've got an event that has an array of key values like so in a column called associated :

 associates: [
     {
       type: a
       person: person1
     },
     {
       type: b
       person: person2
     },
     {
       type: b
       person: person3
     },
     {
       type: c
       person: person3
     }...]

Now I can pull out all of the people associated with an issue doing the following:

| rename associated{}.person as all_associates

And pull out the "first" associate person like so

| eval dathuman=mvindex(all_assoicates, 0)

But, what I want to do is pull out just the associates of a particular type. So, something that get's me all the associates of type "b" only.

What's the best way to do that?

0 Karma
1 Solution

chrisyounger
SplunkTrust
SplunkTrust

Working with MV fields is always a challenge.

Try this:

| makeresults 
| eval _raw = "{\"associates\":[{\"type\":\"a\",\"person\":\"person1\"},{\"type\":\"b\",\"person\":\"person2\"},{\"type\":\"b\",\"person\":\"person3\"},{\"type\":\"c\", \"person\": \"person3\"      }]}" 
| spath 
| rename associates{}.person as person associates{}.type as type 
| eval both=mvzip(person, type, "#####") 
| fields both 
| mvexpand both 
| makemv both delim="#####" 
| eval person=mvindex(both, 0) 
| eval type=mvindex(both, 1)
| search type = "b"
| table person

View solution in original post

chrisyounger
SplunkTrust
SplunkTrust

Working with MV fields is always a challenge.

Try this:

| makeresults 
| eval _raw = "{\"associates\":[{\"type\":\"a\",\"person\":\"person1\"},{\"type\":\"b\",\"person\":\"person2\"},{\"type\":\"b\",\"person\":\"person3\"},{\"type\":\"c\", \"person\": \"person3\"      }]}" 
| spath 
| rename associates{}.person as person associates{}.type as type 
| eval both=mvzip(person, type, "#####") 
| fields both 
| mvexpand both 
| makemv both delim="#####" 
| eval person=mvindex(both, 0) 
| eval type=mvindex(both, 1)
| search type = "b"
| table person

chalbersma
Engager

We ended up solving this on the import of data instead of in the query. But this does indeed work. Thanks!

0 Karma
Get Updates on the Splunk Community!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...