Splunk Search

Extract values from JSON array

DimkoBilanko
Explorer

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
0 Karma

MuS
Legend

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

0 Karma
Get Updates on the Splunk Community!

SplunkTrust Application Period is Officially OPEN!

It's that time, folks! The application/nomination period for the 2025 SplunkTrust is officially open! If you ...

Splunk Answers Content Calendar, June Edition II

Get ready to dive into Splunk Dashboard panels this week! We'll be tackling common questions around ...

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...