Splunk Search

How to build stats on JSON data?

developer_de
New Member

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
0 Karma
1 Solution

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

developer_de
New Member

It works fine .. thanks !!

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...