topic How to get the latencycount by each Api using stats in Splunk Search

How to get the latencycount by each Api using stats

tarunreddy_anth — Mon, 26 Aug 2019 16:33:26 GMT

I am just trying to get the latency count of API by taking the AVG responsetime of the API and using the avg as threshold, for example:

api totalCount AvgRespTime latencyCount
XXX1 250 5sec it should be the count of the API XXX1 which exceeds the AvgRespTime of XXX1
XXX2 300 8sec it should be the count of the API XXX2 which exceeds the AvgRespTime of XXX2.

I am trying the stats command as:

| stats  count(Api) as totalcount avg(time) as AvgRespTime  count(eval(time>avg(time))) by latencycount by Api

Please help me how to get the latencycount by each Api using stats.

Re: How to get the latencycount by each Api using stats

mayurr98 — Mon, 26 Aug 2019 18:40:25 GMT

I am not sure what you are trying to do: your query seems to be wrong and incorrect

do you mean this ?

    | stats count(Api) as totalcount avg(time) as AvgRespTime latest(time) as time by Api 
    | eval latencycount=time-AvgRespTime 
    | fields - time

Re: How to get the latencycount by each Api using stats

solarboyz1 — Mon, 26 Aug 2019 18:50:11 GMT

I think you are want to use eventstats:
https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Eventstats

.....
| eventstats avg(time) AS avgRespTime by api
| where time>avgRespTime

This will calculate the average response time across events, and trigger when that average is exceeded.

Re: How to get the latencycount by each Api using stats

jpolvino — Mon, 26 Aug 2019 18:52:44 GMT

eventstats may help here:

(your search here)
| eventstats avg(duration) AS baseline BY api
| eval overbaseline=if(duration>baseline,"true",null())
| stats count AS totalcount avg(duration) AS AvgRespTime count(overbaseline) AS latencycount BY api

Any event over the baseline (the average) gets a field overbaseline with value true. Otherwise, it gets nothing.

Re: How to get the latencycount by each Api using stats

tarunreddy_anth — Mon, 26 Aug 2019 19:16:32 GMT

Mayurr, I want the count of the APIs that are above the Avg response time of the APIs

Re: How to get the latencycount by each Api using stats

tarunreddy_anth — Mon, 26 Aug 2019 19:29:03 GMT

Hi , Thanks for replying, I am trying to get the count of each APIs which exceeded their independent avg response time.

Re: How to get the latencycount by each Api using stats

tarunreddy_anth — Mon, 26 Aug 2019 19:30:00 GMT

Hi, Jpolvino

The result of latency count is just showing zeros

Re: How to get the latencycount by each Api using stats

tarunreddy_anth — Mon, 26 Aug 2019 19:32:40 GMT

correction:

| stats count(Api) as totalcount avg(time) as AvgRespTime count(eval(time>avg(time))) as latencycount by Api

Re: How to get the latencycount by each Api using stats

jpolvino — Mon, 26 Aug 2019 20:11:48 GMT

Is stats generating any output?

Is your duration in a field named "duration"? If not, then you'll need to replace duration with whatever fields contains the duration you want to measure.

To test, you can also delete lines 3 and 4 from above, and change eventstats to stats, and see if that is working. If it works, then add line 3 (change stats back to eventstats on line 2) and take a look at events above and below each api's baseline value. You should see overbaseline=true for values greater than the api's average.

Re: How to get the latencycount by each Api using stats

solarboyz1 — Mon, 26 Aug 2019 20:17:21 GMT

Add the by api which will give you an average per API.

If you want to aggregate those per run:

| eventstats avg(time) AS avgRespTime by api
| where time>avgRespTime
| stats count as count,  avg(time) as avgRespTime, by api

The eventstats calculates the avg(time) by API and adds it to each event.
The where filters on only those events were the avg was exceeded

The final stats line, is to aggregate whatever statistics are needed from the sessions that exceeded the threshold.