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!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...