Querying about field with JSON type value

Question

I've the follow log:

INFO [http-nio-80-exec-30] class:ControllerV3, M=method, UA=ua, URI=/v3/transactions, QS=limit=21&offset=0&sort=-createDate, V=v3, P=3, RT=50, ET=25, ELAPSE-TIME=50,

REQ={"userId":98745569,"initialCreationDate":"2020-03-13T00:00:00","finalCreationDate":"2020-03-16T15:41:36","source":"SOURCE","statusIds":[2,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,79],"accountingEntryType":"ENTRY_TYPE","considerPartialTransaction":true},

GW=false

So I don't know how to get metrics and data about the REQ JSON field. I want know which values are passed on statusIds, accountingEntryType, considerPartialTransaction and the range of date of initialCreationDate and finalCreationDate. To get metric with normal field I use something like | stats count by UA. I'm newbie with Splunk and I don't know some functions to get the results.

score 1 · Answer 1 · answered Mar 16 '20 at 22:56

Your best bet is to extract the REQ field and then use spath on it to extract the details from the JSON.

To extract the REQ field, you can use the following command. Note that this will not handle nested JSON, but if your events contain that, you can use a different regular expression.

| rex field=raw "REQ=(?<REQ>[^}]+})"

Once you have the REQ field, you can use spath to extract all the fields and values from the JSON, with the following command

| spath input=REQ

The following is an example showing that the extraction and spath work appropriately.

| makeresults | eval raw="
    INFO [http-nio-80-exec-30] class:ControllerV3, M=method, UA=ua, URI=/v3/transactions, QS=limit=21&offset=0&sort=-createDate, V=v3, P=3, RT=50, ET=25, ELAPSE-TIME=50,
    REQ={\"userId\":98745569,\"initialCreationDate\":\"2020-03-13T00:00:00\",\"finalCreationDate\":\"2020-03-16T15:41:36\",\"source\":\"SOURCE\",\"statusIds\":[2,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,79],\"accountingEntryType\":\"ENTRY_TYPE\",\"considerPartialTransaction\":true},
    GW=false
    "
| rex field=raw "REQ=(?<REQ>[^}]+})"
| spath input=REQ

You just need to do `index=blah | rex field=raw "REQ=(?[^}]+})" | spath input=REQ` to get the fields — Simon Duff, Mar 17 '20 at 23:19
so, to get initialCreationDate I can use REQ.initialCreationDate ? — Augusto, Apr 01 '20 at 14:28

Querying about field with JSON type value

1 Answers1