Splunk Search

Count and Time Frames

tkwaller
Builder

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?

Tags (2)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Builder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...