topic Re: Fetch data from json string[] in Splunk Search

Fetch data from json string[]

vickycoder27 — Wed, 15 Jul 2020 13:13:16 GMT

I have a curl response which is json string[], I am able to fetch the data using split(), mvexpand() and then substring. But the problem with substring is, if the sequence of value for a key changes then result is not correct. Tried mvindex on the manipulated data but it doesn't work or rex, not sure if I am doing any thing wrong.

Below is my log and I am interested in "Response []" from which I want to pull userId i.e. 9401850890, 9801850840, 9801850841.

APIName=TestApi, HTTP_STATUS=200, totalTime=2346, Response=[{"id":11168715,"state":"Open","title":"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9401850890"},{"id":11168716,"state":"Closed","title":"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850840"},{"id":21172617,"state":"Verify","title":"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850841"}]

Query that I tried :

index=api_stats source=apistats earliest=-10m@m latest=-0m@m | eval x=ltrim(Response,“[”) | eval n=rtrim(x, “]”) | eval temp=split(n,“}”)| mvexpand temp| eval y=ltrim(temp,“,”) | eval testData=mvindex(y,-1) | eval testId=substr(testData, 7, 9)| eval apiCallerID=substr(testData, 92, 10) | table testData,testId,apiCallerID

Re: Fetch data from json string[]

richgalloway — Wed, 15 Jul 2020 16:41:04 GMT

Perhaps this will help.

| makeresults | eval Response="[{\"id\":11168715,\"state\":\"Open\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9401850890\"},{\"id\":11168716,\"state\":\"Closed\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850840\"},{\"id\":21172617,\"state\":\"Verify\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850841\"}]" ```Above just creates test data``` | eval x=ltrim(Response,"[") | eval n=rtrim(x, "]") | eval temp=split(n,"}") | mvexpand temp | eval y=ltrim(temp,",") | eval testData=mvindex(y,-1) | rex field=testData "id\":(?<testId>\d+)" | rex field=testData "userId\s:\s(?<apiCallerID>\d+)" | table testData,testId,apiCallerID

Re: Fetch data from json string[]

to4kawa — Wed, 15 Jul 2020 20:45:12 GMT

index=_internal |head 1 | table _raw _time | eval _raw="APIName=TestApi, HTTP_STATUS=200, totalTime=2346, Response=[{\"id\":11168715,\"state\":\"Open\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9401850890\"},{\"id\":11168716,\"state\":\"Closed\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850840\"},{\"id\":21172617,\"state\":\"Verify\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850841\"}]" | kv | spath input=Response {} output=response | stats count by response | spath input=response | fields - count response

how about this?

Re: Fetch data from json string[]

vickycoder27 — Thu, 16 Jul 2020 09:40:25 GMT

@to4kawa Much cleaner approach, I will try to explore more of spath().

Re: Fetch data from json string[]

flavioweb1 — Mon, 27 Mar 2023 00:25:34 GMT

I would only suggest a change in the query. change this line | eval temp=split(n,"}") by | eval temp=split(n,"},"). So when assembling a graph, for example, it avoids adding an empty column, for example. Just to clarify, this query saved me, thank you very much.