topic Re: MultiValue Table from Json array in Splunk Search

MultiValue Table from Json array

OualidAn — Thu, 12 Oct 2017 09:55:52 GMT

Hello everyone,

I search a very longtime on internet and splunk doc and i didn't get what i want well i have this Json array :

"LeagueResult": {
"Matchs":
{
"Team": "MANU",
"Date": "2017-09-25T00:00:00",
"Place": "HOM",
"Scored": 0,
"Conceded": 4,
"Difference": -4,
},
{
"Team": "CHE",
"Date": "2017-10-05T00:00:00",
"Place": "AWA",
"Scored": 5,
"Conceded": 4,
"Difference": 1,
},
...

{
"Team": "TOT",
"Date": "2017-10-05T00:00:00",
"Place": "HOM",
"Scored": 1,
"Conceded": 1,
"Difference": 0,
}

And I want to obtain this table or a look-like one :

I used spath function, mvzip function and mvexpand but I didn't succeed.

Re: MultiValue Table from Json array

niketn — Sat, 14 Oct 2017 04:14:32 GMT

@OualidAn, your sample JSON seems to be incorrect each node inside LeagueResults should start with section like Matches in your first subset. Following is a run anywhere search based on your data. PS: first two pipes makeresults and eval generate the mock data, you would not require them with your actual data, provided JSON is formatted correctly.

| makeresults
| eval _raw="{
    \"LeagueResult\": {
        \"Matchs\": {
            \"Team\": \"MANU\",
            \"Date\": \"2017-09-25T00:00:00\",
            \"Place\": \"HOM\",
            \"Scored\": 0,
            \"Conceded\": 4,
            \"Difference\": -4
        },
        \"Matchs\": {
            \"Team\": \"CHE\",
            \"Date\": \"2017-10-05T00:00:00\",
            \"Place\": \"AWA\",
            \"Scored\": 5,
            \"Conceded\": 4,
            \"Difference\": 1
        },
        \"Matchs\": {
            \"Team\": \"TOT\",
            \"Date\": \"2017-10-05T00:00:00\",
            \"Place\": \"HOM\",
            \"Scored\": 1,
            \"Conceded\": 1,
            \"Difference\": 0
        }
    }
}"
| spath
| fields - _raw _time
| rename LeagueResult.Matchs.* as *
| eval data=mvzip(mvzip(mvzip(mvzip(Place,Team),Conceded),Scored),Difference)
| fields data
| mvexpand data
| makemv data delim=","
| eval Place=mvindex(data,0)
| eval Team=mvindex(data,1)
| eval Conceded=mvindex(data,2)
| eval Scored=mvindex(data,3)
| eval Difference=mvindex(data,4)
| table Place Team Scored Conceded Difference

Refer to Splunk documentation on details for working with Multivalue fields:
https://docs.splunk.com/Documentation/Splunk/latest/Search/Parsemultivaluefields
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions

Re: MultiValue Table from Json array

OualidAn — Mon, 16 Oct 2017 09:38:12 GMT

thank you that what i was looking for, it works perfectly now !