Splunk Search

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

anasar
New Member

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"
      }
   }
}
Tags (2)
0 Karma
1 Solution

javiergn
Super Champion

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

View solution in original post

0 Karma

javiergn
Super Champion

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

0 Karma

anasar
New Member

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.

0 Karma

javiergn
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

Enhance Your Splunk App Development: New Tools & Support

UCC FrameworkAdd-on Builder has been around for quite some time. It helps build Splunk apps faster, but it ...

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...