Finding the AVG for requests over 3 seconds

[joe06031990]

Hi,

I am trying to get the AVG response time for calls over 3 seconds and have the bellow:

index=test sourcetype="test"
| bin span=1d _time
| table response_time
| eventstats count as Event
| eval ResponseTime=response_time/1000
| eval isOver3s=if(ResponseTime>3,1,0)
| stats values(Event) as "Event",avg(ResponseTime) as "Average Response" ,sum(isOver3s) as "isOver3s" max(ResponseTime) as "Max Response Time" avg(eval(ResponseTime>=3)) as avgisOver3s
| eval Percentage=round((isOver3s/Event)*100,2)
| table Event "Average Response" isOver3s Percentage ,"Max Response Time", avgisOver3s

However the AVG response for the over 3 seconds is less than the normal AVG which is incorrect.

Any help would be greatly Appreciated.

Thanks,

Joe  

[PickleRick · Accepted solution]

You're overcomplicating things 🙂

Firstly - why the bin and table?

Secondly - it should be enough to "scale" the response_time down (you can do this before or after statsing) and then stats. That's it.

There's one trick about the eval though. It seems that eval produces an actual numerical result which is fine with count but seems to break some more fancy stats functions (like avg) so effectively you're doing an avg of 1 (because that's what it gets cast to). You got 1 as the result of your avg, right? So in order to get any reasonable value you have to do some neat replacement.

index=test sourcetype="test"
| eval RT=response_time/1000
| stats count AS "Number of Events"
avg(RT) as "Average Response Time"
count(eval(RT>3)) AS "Number of long responses"
max(RT) AS "Maximum response time"
avg(eval(if(RT>3,RT,null()))) AS "Average long response"

And so on.

[joe06031990]

Thanks for your help.