How to extract fields from JSON data based on API calls?

[anasar]

Hi

I have the below json file in Splunk. How do I extract based on api calls? Eg.

apiname                 count200   count500   avgresponse-time
HomeLoanService         150        2          0.02
CreditAccountServvice   323        19         0.04
....

avgresponse time is = sum(avgresponse_time)/sum(count_200)

Each json event:

{
   "date_time":"30/Jan/2016:00:00:20-0800",
   "total_events":13,
   "api_calls":{
      "HomeLoanService":{
         "count_200":1,
         "count_500":0,
         "avg_response_time":"0.0190"
      },
      "CreditAcccountService":{
         "count_200":2,
         "count_500":0,
         "avg_response_time":"0.538"
      },
      "RegisterService":{
         "count_200":1,
         "count_500":0,
         "avg_response_time":"0.0470"
      },
      "TransactionService":{
         "count_200":1,
         "count_500":0,
         "avg_response_time":"0.186"
      },
      "getNewUsers":{
         "count_200":2,
         "count_500":0,
         "avg_response_time":"0.0620"
      },
      "LockServices":{
         "count_200":1,
         "count_500":0,
         "avg_response_time":"0.0200"
      },
      "ValidateService":{
         "count_200":4,
         "count_500":1,
         "avg_response_time":"0.0210"
      }
   }
}

[javiergn · Accepted solution]

Taking your example as above this is what I've done (you can ignore the stats count as this is needed to generate a dummy row):

| stats count
| eval myjson = "
{
\"date_time\":\"30/Jan/2016:00:00:20-0800\",
\"total_events\":13,
\"api_calls\":{
\"HomeLoanService\":{
\"count_200\":1,
\"count_500\":0,
\"avg_response_time\":\"0.0190\"
},
\"CreditAcccountService\":{
\"count_200\":2,
\"count_500\":0,
\"avg_response_time\":\"0.538\"
},
\"RegisterService\":{
\"count_200\":1,
\"count_500\":0,
\"avg_response_time\":\"0.0470\"
},
\"TransactionService\":{
\"count_200\":1,
\"count_500\":0,
\"avg_response_time\":\"0.186\"
},
\"getNewUsers\":{
\"count_200\":2,
\"count_500\":0,
\"avg_response_time\":\"0.0620\"
},
\"LockServices\":{
\"count_200\":1,
\"count_500\":0,
\"avg_response_time\":\"0.0200\"
},
\"ValidateService\":{
\"count_200\":4,
\"count_500\":1,
\"avg_response_time\":\"0.0210\"
}
}
}"
| spath input=myjson
| fields *avg_response_time, *count_200
| foreach *avg_response_time [eval total_avg_response_time = '<<FIELD>>']
| foreach *count_200 [eval total_count_200 = '<<FIELD>>']
| fields total_avg_response_time, total_count_200
| eval avg_response_time = total_avg_response_time / total_count_200

And the output:

total_avg_response_time     total_count_200     avg_response_time
0.0620  2   0.0310 

Hope that helps

[anasar]

Hi,

This event is already indexed. I've just shown a single record(event). If we have multiple such events, pls let me know how to consolidate.

[javiergn]

If you JSON events have already been extracted correctly then you just need the following bits:

 your search query here
 | fields *avg_response_time, *count_200
 | foreach *avg_response_time [eval total_avg_response_time = '<<FIELD>>']
 | foreach *count_200 [eval total_count_200 = '<<FIELD>>']
 | fields total_avg_response_time, total_count_200
 | eval avg_response_time = total_avg_response_time / total_count_200

If there are multiple events the above should still compute the avg_response_time per event.
Please let me know if that's what you are looking for.

Thanks,
J