Getting Data In

How can I extract the JSON data as key value pair?

soumyacharya91
Path Finder

Hi,

I have extracted the JSON data. After data indexed I found that one field contains another format of JSON data which is indexed as a string. Please help me in extracting the data. Please find the log details below which I had received from indexer after the indexing.

{"field1": "value1", "field2": "value2", "field3": "value3", "field4": "{\"subfield\":\"value\",\"subfield\":\"value\"}", "field": "value"}

Please help me in extracting the data as key value pair which is present in the field4 . Rest fields are able to parse the data correctly.

Thanks,
Sam

0 Karma

woodcock
Esteemed Legend

Try this:

| makeresults 
| eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield\\\":\\\"value1\\\",\\\"subfield\\\":\\\"value2\\\"}\", \"field\": \"value\"}" 
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution" 
| rex max_match=0 "\\\\\"subfield\\\\\":\s*\\\\\"(?<field4>[^\\\\\"]+)"

This RegEx string is not dependent on the spath so it can be used in props.conf directly.

0 Karma

vsai0718
Path Finder

You can try this one
| rename _raw AS _temp field4 AS _raw | extract pairdelim="?&" kvdelim="=" | rename _raw AS field4 _temp AS _raw

0 Karma

TISKAR
Builder

@soumyacharya91, can you try this:

   | makeresults 
       | eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield\\\":\\\"value1\\\",\\\"subfield\\\":\\\"value2\\\"}\", \"field\": \"value\"}"
       | extract 
       | rex field=field4 "\"subfield\":\s*\"(?<subfield1>[^\"]+)\",\"subfield\":\s*\"(?<subfield2>[^\"]+)"
0 Karma

soumyacharya91
Path Finder

Hi,

This is not working.

0 Karma

vsai0718
Path Finder

| rename _raw AS _temp field4 AS _raw | extract pairdelim="?&" kvdelim="=" | rename _raw AS field4 _temp AS _raw

You can try this, it extracts all the nested key, value pairs at search time

0 Karma

TISKAR
Builder

So try this

| makeresults 
          | eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield\\\":\\\"value1\\\",\\\"subfield\\\":\\\"value2\\\"}\", \"field\": \"value\"}"
          | extract 
          | rex field=field4 "\"subfield\":\s*\"(?<subfield1>[^\"]+)\",\"subfield\":\s*\"(?<subfield2>[^\"]+)"
0 Karma

woodcock
Esteemed Legend

I am not sure that I get exactly what you need but try this:

| makeresults 
| eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield\\\":\\\"value1\\\",\\\"subfield\\\":\\\"value2\\\"}\", \"field\": \"value\"}"

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| spath field4
| rex field=field4 max_match=0 "\"subfield\":\s*\"(?<field4>[^\"]+)"

soumyacharya91
Path Finder

Hi woodcock,

I have tried the solution but it is not working.

I tried below query along with my base search and checked it is properly extracting the data but I don't know how to apply this in splunk backend files. Is there any way we can apply this solution to props.conf / transforms.conf

|spath input = field4

Thanks,
Sam

0 Karma

woodcock
Esteemed Legend

See my other answer.

0 Karma

somesoni2
Revered Legend

Give this a try (first two lines are to generate sample data)

| gentimes start=-1 | eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield1\\\":\\\"value\\\",\\\"subfield2\\\":\\\"value\\\"}\", \"field\": \"value\"}" | table _raw 
| rex field=_raw mode=sed "s/\\\\"/"/g s/\"\{/[{/ s/\}\"/}]/"| spath
0 Karma

soumyacharya91
Path Finder

Hi,

It is not working.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...