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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...