Splunk Search

Finding max of a count up to each hour of the day

bjmclean
Explorer

I am wrestling with a query around getting a max value of a count per hour up to each. I will explain with an example...

Hour 1 - 400 visitors
Hour 2 - 200 visitors
Hour 3 - 250 visitors
Hour 4 - 100 visitors
Hour 5 - 700 visitors
Hour 6 - 900 visitors

I would like to calculate the max for each hour UP TO that hour. So for Hour 1, max would be 400. For Hour 2, the max will also be 400 since that is the max up to hour 2 (between 400 and 200). The max would be 400 for Hours 3 and 4 as well. Hour 5 would have a max of 700 visitors. Hour 6 would have a max of 900 visitors.

The structure of data I'm working with is...
Hour Visitor_Count
1 400
2 200
...

I would like the max per hour up to each hour to be column 3.

The one thing I've managed to do is calculate the max manually for each hour

| eventstats sum(eval(if(hr<=7,visitor_count,null()))) as max_for_hr_7

This works, but creates a column for each hour, and replicates the values for each column down all of the rows, so not ideal...
Hour Visitor_Count max_for_hr_1 max_for_hr_2 max_for_hr_3 ....

Any thoughts on how I could achieve adding one additional column with the max of visitor count up to each hour?

Thank you in advance!

0 Karma
1 Solution

maciep
Champion

I think you want streamstats

  ...  | streamstats max(Visitor_Count) as Max_So_Far

just be sure to sort the results appropriately first if needed...

View solution in original post

0 Karma

maciep
Champion

I think you want streamstats

  ...  | streamstats max(Visitor_Count) as Max_So_Far

just be sure to sort the results appropriately first if needed...

0 Karma

bjmclean
Explorer

Thank you @maciep! That worked perfectly!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...