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

[soumyacharya91]

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

[woodcock]

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.

[vsai0718]

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

[TISKAR]

@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>[^\"]+)"

[soumyacharya91]

Hi,

This is not working.

[vsai0718]

| 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

[TISKAR]

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>[^\"]+)"

[woodcock]

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]

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

[woodcock]

See my other answer.

[somesoni2]

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

[soumyacharya91]

Hi,

It is not working.