topic Re: Splitting multi-value field (json) in Getting Data In

Splitting multi-value field (json)

nwenzl_splunk — Fri, 28 Feb 2020 14:40:38 GMT

Hello Splunkers,

So I am having trouble with some json nested arrays that contain multiple latitude and longitude in one event.
Is there any way that I can split this one event up into 4 single events?

Re: Splitting multi-value field (json)

codebuilder — Fri, 28 Feb 2020 15:29:14 GMT

Yes, use mvexpand.

https://docs.splunk.com/Documentation/Splunk/8.0.2/SearchReference/Mvexpand

Re: Splitting multi-value field (json)

to4kawa — Fri, 28 Feb 2020 18:14:38 GMT

your_search
| spath "events.data{}.place.location.latitude" output=event.latitude 
| spath "events.data{}.place.location.longitude" output=event.longitude
| table event.latitude event.longitude
| eval counter=mvrange(0,mvcount(event.latitude))
| mvexpand counter
| rename counter as _counter
| foreach * [ eval <<FIELD>> = mvindex('<<FIELD>>', _counter) ]
| fields - _counter

Re: Splitting multi-value field (json)

rsantkumar — Wed, 30 Sep 2020 04:29:24 GMT

Hi to4kawa,

Thanks for your input your comment helped me a lot. I am doing something similar but unable to succeed after multiple attempts.

I am parsing a JSON to fetch plugin names, version and release dates.
SO each plugin can have one or more release versions available

I am unable to expand the plugin names nomatter what i do:

please see below:

Plugin Plugin_Version Release_Date
aemrules 1.0 2020-02-07
csharp 8.3 (build 14607) 2020-02-05
cpp 8.4 (build 15306) 2020-02-21
6.0.2 (build 20657) 2020-01-31
6.1 (build 20866) 2020-02-14

Ideally it should be:

Plugin Plugin_Version Release_Date
aemrules 1.0 2020-02-07
csharp 8.3 (build 14607) 2020-02-05
csharp 8.4 (build 15306) 2020-02-21
cpp 6.0.2 (build 20657) 2020-01-31
cpp 6.1 (build 20866) 2020-02-14

the query that i use is :

basequery|eval json_field = _raw
| spath input=json_field path=data.plugins{}.key output=Plugin
| spath input=json_field path=data.plugins{}.updates{}.release{}.version output=Plugin_Version
|spath input=json_field path=data.plugins{}.updates{}.release{}.date output=Release_Date
| eval counter=mvrange(0,mvcount(Plugin_Version))
| mvexpand counter
| rename counter as _counter
| foreach * [ eval <> = mvindex('<>', _counter) ]
| fields - _counter
| table Plugin Plugin_Version Release_Date

Re: Splitting multi-value field (json)

to4kawa — Thu, 05 Mar 2020 02:08:10 GMT

Hi, @rsantkumar

basequery 
| spath path=data.plugins{} output=Plugins
| stats count by Plugins
| spath input=Plugins
| rename updates{}.release{}.* as *
| table key version date
| rename key as Plugin, version as Plugin_Version, date as Release_Date

key is not same mvcount with version and date.
I'm not sure your logs. maybe works.

Re: Splitting multi-value field (json)

nwenzl_splunk — Wed, 30 Sep 2020 04:29:55 GMT

Update:

I got it to work by first combining the respective coordinates with mvzip, then breaking the pairs apart again with mvexpand and finally creating latitude and longitude fields with regex capture groups. Hope it can help somebody else!

| head 1
| spath output=event.latitude path=events.data{}.place.location.latitude
| spath output=event.longitude path=events.data{}.place.location.longitude
| table event.latitude, event.longitude
| eval test = mvzip('event.latitude', 'event.longitude', ";")
| fields - event.*
| mvexpand test
| rex field=test "(?<latitude>.);(?<*longitude>.*)"
| fields - test
| geostats latfield=latitude longfield=longitude count