Getting Data In

How to convert JSON Keys and values as columns in splunk

sdaruna
Explorer

Hi,

I want to flatten json data to columns for my report purpose. I might not be explaining my requirement properly, here is what my data and result has to be.

Input:

{
"name" : "srini",
"value" {
"1": "val1",
"2" : "val2",
"3" : "val3"
}
}

Output:

name, name.key, name.value
------------------------------------------
srini      1         val1
srini      2         val2
srini      3         val3
Tags (1)
0 Karma
1 Solution

javiergn
Super Champion

I think your JSON is wrong and there's a colon missing after value.
In any case, see if the below helps:

| makeresults
| eval json = "
{
   \"name\" : \"srini\",
   \"value\": {
      \"1\": \"val1\",
      \"2\" : \"val2\",
      \"3\" : \"val3\"
   }
}
"
| spath input=json
| fields - json
| untable name key value
| rex field=key "(?<key>\d+)"
| rename key AS name.key, value AS name.value

Output (see picture below):

alt text

View solution in original post

0 Karma

javiergn
Super Champion

I think your JSON is wrong and there's a colon missing after value.
In any case, see if the below helps:

| makeresults
| eval json = "
{
   \"name\" : \"srini\",
   \"value\": {
      \"1\": \"val1\",
      \"2\" : \"val2\",
      \"3\" : \"val3\"
   }
}
"
| spath input=json
| fields - json
| untable name key value
| rex field=key "(?<key>\d+)"
| rename key AS name.key, value AS name.value

Output (see picture below):

alt text

0 Karma

jkat54
SplunkTrust
SplunkTrust
0 Karma

sdaruna
Explorer

I have so many key value pairs under "value" field. So it is not possible to specify each and every key of "value" field.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...