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!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...