splunksplunk-query

How to show nested structures in Splunk table


Let's say I have the following kind of event:

{"title":"title1","movieStatistics":[{"country":"US","cast":[{"name":"Actor1","age":30,"languages":["English","Spanish"]},{"name":"Actor2","age":48,"languages":["English","Polish","Spanish"]}]}]}

With the following search: index="index-blabla" | table "movieStatistics{}.cast{}.languages{}" "movieStatistics{}.cast{}.age"

I obtain this table:

movieStatistics{}.cast{}.languages{} movieStatistics{}.cast{}.age
English
Spanish
English
Polish
Spanish
30
48

Which is not easy to exploit as I cannot really associate the "age" and the "languages" (I know it doesn't make sense, but it is just for the sake of the example).

Could you help me find the appropriate search in order to have a table like the following:

movieStatistics{}.cast{}.languages{} movieStatistics{}.cast{}.age
English
Spanish
30
English
Polish
Spanish
48

I tried the mvexpand, but it looks like it doesn't fit my need.


Solution

  • If you use spath to parse the _raw and get all the stuff broken down, then that is too late to use mvexpand. You need to use mvexpand before you break down the content of the array elements:

    | spath movieStatistics{}.cast{}
    | mvexpand movieStatistics{}.cast{}
    | spath input=movieStatistics{}.cast{}
    

    What that does is spath the movieStatistics{}.cast{} array and then do the mvexpand to separate all the parts of the array and then spath the different records to get separate events for each array element.

    Here is a run-anywhere example:

    | makeresults
    | eval _raw="{\"title\":\"title1\",\"movieStatistics\":[{\"country\":\"US\",\"cast\":[{\"name\":\"Actor1\",\"age\":30,\"languages\":[\"English\",\"Spanish\"]},{\"name\":\"Actor2\",\"age\":48,\"languages\":[\"English\",\"Polish\",\"Spanish\"]}]}]}"
    | spath movieStatistics{}.cast{}
    | mvexpand movieStatistics{}.cast{}
    | spath input=movieStatistics{}.cast{}
    | fields - _raw _time movieStatistics{}.cast{}
    | table *