How to get the latencycount by each Api using stats

[tarunreddy_anth]

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.

[tarunreddy_anth]

correction:

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

[jpolvino]

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.

[tarunreddy_anth]

Hi, Jpolvino

The result of latency count is just showing zeros

[jpolvino]

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.

[solarboyz1]

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.

[tarunreddy_anth]

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

[solarboyz1]

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.

[mayurr98]

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

[tarunreddy_anth]

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