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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...