Extract values from JSON array

[DimkoBilanko]

Hi everyone!
I have a JSON output in raw format:

{"result":{"addr":"456hR5drYrYrdY5wTYreYrdyerYe6y","workers":[["host04",{},29,1,"80000",0,22],["client3001",{"a":"0.27"},1,1,"80000",0,22],["host02",{"a":"0"},16,1,"80000",0,22],["host06",{"a":"0.27"},4,1,"80000",0,22],["client52",{"a":"0.27"},10,1,"80000",0,22],["host03",{"a":"0.54"},5,1,"80000",0,22],["host01",{"a":"0.54"},26,1,"80000",0,22],["host08",{"a":"0.53"},3,1,"80000",0,22],["f05",{},19,1,"80000",0,22],["client4004",{"a":"0.27"},76,1,"80000",0,22],["host05",{"a":"0.54"},36,1,"80000",0,22],["host07",{},6,1,"80000",0,22],["client5004",{},2,1,"80000",0,22],["client3002",{"a":"0.27"},7,1,"80000",0,22],["client4003",{"a":"0"},111,1,"80000",0,22],["host02",{"a":"0.54"},25,1,"80000",0,22],["client9006",{"a":"0.53"},21,1,"80000",0,22],["client6001",{"a":"0.55"},9,1,"80000",0,22],["P4003",{"a":"478.71"},1937,1,"256",0,24],["P6001",{"a":"349.75"},1936,1,"256",0,24],["p9006",{"a":"225.7"},1936,1,"128",0,24],["P5004",{"a":"369.91"},1936,1,"128",0,24],["P3002",{"a":"522.23"},1937,1,"256",0,24],["P52",{"a":"449.7"},794,1,"256",0,24],["P4004",{"a":"551.24"},1643,1,"256",0,24],["P6004",{"a":"406.18"},1936,1,"256",0,24],["P3001",{"a":"377.17"},1788,1,"256",0,24]],"algo":-1},"method":"stats.provider.workers"}

Here are in some readable view:

{"result":
    {
    "addr":"456hR5drYrYrdY5wTYreYrdyerYe6y",
    "workers":
    [
        ["host07",     {"a":"0.53"},    48,     1,  "80000",    0,  22],
        ["client52",     {},                5,      1,  "80000",    0,  22],
        ["host06",     {"a":"0.27"},    26,     1,  "80000",    0,  22],
        ["client3002",   {"a":"0"},     8,      1,  "80000",    0,  22],
        ["client4004",   {},                0,      1,  "80000",    0,  22],
        ["host08",     {"a":"0.27"},    9,      1,  "80000",    0,  22],
        ["host02",     {"a":"0.53"},    19,     1,  "80000",    0,  22],
        ["client5004",   {"a":"0.27"},  28,     1,  "80000",    0,  22],
        ["host01",     {"a":"0.27"},    16,     1,  "80000",    0,  22],
        ["client6001",   {"a":"0.53"},  45,     1,  "80000",    0,  22],
        ["client9006",   {"a":"0.53"},  26,     1,  "80000",    0,  22],
        ["host03",       {"a":"0"},     118,    1,  "80000",    0,  22],
        ["host02",       {"a":"0.27"},  78,     1,  "80000",    0,  22],
        ["f05",       {},               1,      1,  "80000",    0,  22],
        ["host05",       {"a":"0.27"},  10,     1,  "80000",    0,  22],
        ["client4003",   {"a":"0.54"},  25,     1,  "80000",    0,  22],
        ["host04",       {"a":"1.34"},  12,     1,  "80000",    0,  22],
        ["client3001",   {"a":"0.54"},  16,     1,  "80000",    0,  22]
    ],"algo":22}
,"method":"stats.provider.workers"}

I want to get names and count the number of workers in each event.
But automatically Splunk get "result.workers{}{}" field that contains all values in line:

0
1
22
80000
24
256
1986
1987
29
host02

In output I want to get table like:

Name          a          value1     value2     value3     value4     value5
---------------------------------------------------------------------------------------------------------
host07        0.53       48         1          80000      0          22
client52      0.55       51         1          80000      0          22
host06        0.27       26         1          80000      0          22
....
client3002    0          8          1          80000      0          22

[MuS]

Hi DimkoBilanko,

using the following search I was able to produce the table result you expected:

| makeresults 
| eval _raw="{\"result\":{\"addr\":\"456hR5drYrYrdY5wTYreYrdyerYe6y\",\"workers\":[[\"host04\",{},29,1,\"80000\",0,22],[\"client3001\",{\"a\":\"0.27\"},1,1,\"80000\",0,22],[\"host02\",{\"a\":\"0\"},16,1,\"80000\",0,22],[\"host06\",{\"a\":\"0.27\"},4,1,\"80000\",0,22],[\"client52\",{\"a\":\"0.27\"},10,1,\"80000\",0,22],[\"host03\",{\"a\":\"0.54\"},5,1,\"80000\",0,22],[\"host01\",{\"a\":\"0.54\"},26,1,\"80000\",0,22],[\"host08\",{\"a\":\"0.53\"},3,1,\"80000\",0,22],[\"f05\",{},19,1,\"80000\",0,22],[\"client4004\",{\"a\":\"0.27\"},76,1,\"80000\",0,22],[\"host05\",{\"a\":\"0.54\"},36,1,\"80000\",0,22],[\"host07\",{},6,1,\"80000\",0,22],[\"client5004\",{},2,1,\"80000\",0,22],[\"client3002\",{\"a\":\"0.27\"},7,1,\"80000\",0,22],[\"client4003\",{\"a\":\"0\"},111,1,\"80000\",0,22],[\"host02\",{\"a\":\"0.54\"},25,1,\"80000\",0,22],[\"client9006\",{\"a\":\"0.53\"},21,1,\"80000\",0,22],[\"client6001\",{\"a\":\"0.55\"},9,1,\"80000\",0,22],[\"P4003\",{\"a\":\"478.71\"},1937,1,\"256\",0,24],[\"P6001\",{\"a\":\"349.75\"},1936,1,\"256\",0,24],[\"p9006\",{\"a\":\"225.7\"},1936,1,\"128\",0,24],[\"P5004\",{\"a\":\"369.91\"},1936,1,\"128\",0,24],[\"P3002\",{\"a\":\"522.23\"},1937,1,\"256\",0,24],[\"P52\",{\"a\":\"449.7\"},794,1,\"256\",0,24],[\"P4004\",{\"a\":\"551.24\"},1643,1,\"256\",0,24],[\"P6004\",{\"a\":\"406.18\"},1936,1,\"256\",0,24],[\"P3001\",{\"a\":\"377.17\"},1788,1,\"256\",0,24]],\"algo\":-1},\"method\":\"stats.provider.workers\"}" 
| spath 
| table result.workers{}{} 
| rename COMMENT AS "Above code is used to create events" 
| rename result.workers{}{} AS results 
| makemv results 
| mvexpand results 
| rex field=results max_match=0 "(?<_raw>\w+\d+\s(\d+\s){4}\d+)" 
| stats values(*) AS * by _raw 
| rex "(?<host>\w+\d+)\s(?<value1>\d+)\s(?<value2>\d+)\s(?<value3>\d+)\s(?<value4>\d+)\s(?<value5>\d+)" 
| fields - _raw results

This may work for a small set of events, but can break for a larger set of events because of the mvexpand command.

I would suggest to use props.conf to create a search time field extraction using this rex

(?:\[\[|,\[)\"(?<host>\w+\d+)\",(?:\{\}|\{\"a\":\"(?<value1>[^\"]+)\"\}),(?<value2>[^,]+),(?<value3>[^,]+),\"(?<value4>[^\"]+)\",(?<value5>[^,]+),(?<value6>[^\]]+)

to create the fields and values.

Hope this helps ...

cheers, MuS