Splunk Search

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

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?

Tags (4)
1 Solution
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...

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...

Explorer

Thank you @maciep! That worked perfectly!

Get Updates on the Splunk Community!

#### Splunk Security Content for Threat Detection & Response, Q1 Roundup

Join Principal Threat Researcher, Michael Haag, as he walks through:An introduction to the Splunk Threat ...

#### Splunk Life | Happy Pride Month!

Happy Pride Month, Splunk Community! &#x1f308; In the United States, as well as many countries around the ...

#### SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...