topic Re: How to build stats on JSON data? in Splunk Search

How to build stats on JSON data?

developer_de — Mon, 28 May 2018 20:21:52 GMT

Hi,
I would like to get help on applying stats on the following JSON data:

 {
    "ts":1527498793267,
    "version":"1.12.7",
    "data":{
       "myList":[
          {
             "id":"180528_090203",
             "nativeRequestReceived":1,
             "nativeResponseSent":1
          },
          {
             "id":"180528_090129",
             "nativeRequestReceived":1,
             "nativeResponseSent":1
          }
       ],
       "freemem":6534152192
    },
    "time":"2018-05-28T09:13:13.267Z",
    "v":0
 }

I started writing the query as written below, but it doesn't give desired result. split function is grouping same values of nativeResponseSent and nativeRequestReceived together.

input data | rename data.myList{}.* as * | eval mvfield=mvzip($id$,mvzip($nativeResponseSent$,$nativeRequestReceived$)) | fields mvfield | mvexpand mvfield | eval mvfield=split(mvfield,",") | eval id=mvindex(mvfield,0) | eval nativeResponseSent=mvindex(mvfield,1) | eval nativeRequestReceived=mvindex(mvfield,2) | stats sum(nativeResponseSent) sum(nativeRequestReceived) by id

Re: How to build stats on JSON data?

niketn — Mon, 28 May 2018 21:30:28 GMT

@developer_de, what is the desired output? Following is the output I get when I try the following run anywhere search with the data similar to that provided by you.

id             nativeResponseSent   nativeRequestReceived
180528_090129   3                   1
180528_090203   1                   2

Following is the run anywhere search with dummy data as per the question:

| makeresults 
| eval _raw="{
     \"ts\":1527498793267,
     \"version\":\"1.12.7\",
     \"data\":{
        \"myList\":[
           {
              \"id\":\"180528_090203\",
              \"nativeRequestReceived\":2,
              \"nativeResponseSent\":1
           },
           {
              \"id\":\"180528_090129\",
              \"nativeRequestReceived\":1,
              \"nativeResponseSent\":3
           }
        ],
        \"freemem\":6534152192
     },
     \"time\":\"2018-05-28T09:13:13.267Z\",
     \"v\":0
  }" 
| spath 
| rename data.myList{}.* as *
| eval mvfield=mvzip(id,mvzip(nativeResponseSent,nativeRequestReceived))
| fields - _*
| fields mvfield 
| mvexpand mvfield
| makemv mvfield delim=","
| eval id=mvindex(mvfield,0), nativeResponseSent=mvindex(mvfield,1), nativeRequestReceived=mvindex(mvfield,2)
| fields - mvfield
| stats sum(nativeResponseSent) as nativeResponseSent sum(nativeRequestReceived) as nativeRequestReceived by id

Re: How to build stats on JSON data?

developer_de — Mon, 28 May 2018 22:18:07 GMT

It works fine .. thanks !!

Re: How to build stats on JSON data?

niketn — Tue, 29 May 2018 05:13:05 GMT

@developer_de I have converted my comment to answer. Please accept to mark this as answered.