Count and Time Frames

[tkwaller]

Hello

eventtype=tt-APIGatewayAthenticationFail earliest=-30d | bucket _time span=1h | stats count by _time | eval Average=if(_time>relative_time(_time,"-1h"),count,null()) | eventstats avg(count) as avgCount by _time | timechart perc90(avgCount) as Avg_90 avg(Average) as Average

I'm probably making this harder than it really is but I'm trying to get the 90th percentile average for the count over 30 days by day. Along with this I am also trying to get the average of the count by hour. So 90th percentile average over 30 days compared to the hourly average of the count and be able to graph them.

Essentially I want to take all the hourly averages going back 30 days and then get the 90th percentile of them. And the average duration from the last hour has to come along for the ride.

The above is mostly correct it functions but its not quite right.

This one works but doesn't allow me to use two separate time frames that I need "90th percentile average over 30 days compared to the hourly average of the count"

eventtype=tt-APIGatewayAthenticationFail earliest=-30d| bucket _time span=1h | stats count by _time | timechart avg(count) as Average p90(count) as Average_90 

Any suggestions or pointers?

[somesoni2 · Accepted solution]

Try this.

eventtype=tt-APIGatewayAthenticationFail earliest=-30d| eval date=strftime(_time,"%m/%d/%Y")| bucket _time span=1h | stats count by _time,date |  eventstats avg(count) as AvgDay by date | eventstats p90(AvgDay) as Avg90_Month | fields - AvgDay, date

[tkwaller]

Ah yes of course, I was heading in the wrong direction. Yes this is good. Thank you!