Splunk Search

Fetch data from json string[]

vickycoder27
Explorer

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

Labels (1)
Tags (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

to4kawa
Ultra Champion
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?

vickycoder27
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!