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?
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
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
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
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.
Yes, use mvexpand.
https://docs.splunk.com/Documentation/Splunk/8.0.2/SearchReference/Mvexpand