topic Re: How do I extract multiple key values from a double quoted JSON? in Splunk Search

How do I extract multiple key values from a double quoted JSON?

baskarkrishnanc — Thu, 04 Oct 2018 16:08:45 GMT

I have data in splunk as following:

log:     [INFO ] 17:01:43.572 : [main] o.a.k.c.Processor:process(103): response body from MYSERVICE : {"uniqueNumber": "12345", "key-2": "value-2",.. "key-n": "value-n", "returnCode": "A12"}

and I am trying to extract key values pairs from double quoted json as below:

myquery "response body from MYSERVICE" |  rex "\"uniqueNumber\":\s\"(?<unumber>.*)\"" |  rex "\"returnCode\":\s\"(?<retcode>.*)\"" | table unumber retcode

I am expecting to populate the below table:

+---------+---------+
| unumber | retcode |
+---------+---------+
|  123455 | A12     |
|  123456 | A10     |
|  123457 | A03     |
|  123458 | A01     |
+---------+---------+

There is a space between key value pairs after the colon which I tried to match using \s but it generates an empty table.
Any ideas ?

PS: I am using Splunk Enterprise 7.1.2.

Re: How do I extract multiple key values from a double quoted JSON?

493669 — Thu, 04 Oct 2018 16:36:07 GMT

I tried this with small change in regex and it is working -
Try below run anywhere search-

| makeresults |eval _raw=" log:     [INFO ] 17:01:43.572 : [main] o.a.k.c.Processor:process(103): response body from MYSERVICE : {\"uniqueNumber\": \"12345\", \"key-2\": \"value-2\",.. \"key-n\": \"value-n\", \"returnCode\": \"A12\"}"|  rex "\"uniqueNumber\":\s\"(?<unumber>[^\"]+)" |  rex "\"returnCode\":\s\"(?<retcode>[^\"]+)"

Re: How do I extract multiple key values from a double quoted JSON?

darrenfuller — Thu, 04 Oct 2018 16:50:16 GMT

you could also grab the json into a field and spath the results...

like so:

| makeresults 
| eval _raw="[INFO ] 17:01:43.572 : [main] o.a.k.c.Processor:process(103): response body from MYSERVICE : {\"uniqueNumber\": \"12345\", \"key-2\": \"value-2\", \"key-n\": \"value-n\", \"returnCode\": \"A12\"}" 
| rex field=_raw "response body from \w+ \: (?<json>\{.+\})$"
| spath input=json

outputs:

**_time**           
2018-10-04 12:48:35

**_raw**  
[INFO ] 17:01:43.572 : [main] o.a.k.c.Processor:process(103): response body from MYSERVICE : {"uniqueNumber": "12345", "key-2": "value-2", "key-n": "value-n", "returnCode": "A12"}

json
{"uniqueNumber": "12345", "key-2": "value-2", "key-n": "value-n", "returnCode": "A12"}  

**key-2**
value-2 

**key-n**
value-n 

**returnCode**
A12

Re: How do I extract multiple key values from a double quoted JSON?

baskarkrishnanc — Thu, 04 Oct 2018 18:45:04 GMT

Thank you. This works as-is, but if I add this regex to my query, it is not working. I need to specify the index and search query to make it dynamic, such as

index=myidx "MYNEWSERVICE" "response body from MYSERVICE" | rex "\"uniqueNumber\":\s\"(?<unumber>[^\"]+)" |  rex "\"returnCode\":\s\"(?<retcode>[^\"]+)" | table retcode unumber

Is that possible to skip eval as I need to use dynamic query results ?

Re: How do I extract multiple key values from a double quoted JSON?

baskarkrishnanc — Tue, 29 Sep 2020 21:33:31 GMT

I made some more changes in regex and it works now. I had to escape the escape backslash as this is how Splunk showed as raw text like,\"returnCode\": \"A01\"}\n","stream":"stdout","time":"2018-12-06T22:01:43.653111599Z"}. Not sure why Splunk escapes this way and deferring this to experts.

Final query looks like
index=myidx "mysearchstring" "response body from MYSERVICE" | rex field=_raw "uniqueNumber[^\"]+\":\s*[^\"]+\"(?[^\\\"]+)" | rex field=_raw "returnCode[^\"]+\":\s*[^\"]+\"(?[^\\\"]+)" | table uniqueNumber Return_Code

Re: How do I extract multiple key values from a double quoted JSON?

baskarkrishnanc — Thu, 04 Oct 2018 20:47:35 GMT

I tried to use spath but I had to make more regex changes so I went with regex. Thank you for your help!

Re: How do I extract multiple key values from a double quoted JSON?

493669 — Fri, 05 Oct 2018 03:47:18 GMT

|makeresults was generating command used just to test sample data ...you will use rex command after index=...

Re: How do I extract multiple key values from a double quoted JSON?

baskarkrishnanc — Fri, 05 Oct 2018 15:47:35 GMT

Thank you!