topic Re: Max of peak hour volume in Getting Data In

Max of peak hour volume

th1agarajan — Sat, 23 Aug 2014 00:48:20 GMT

I need the Max of peak hour volume

My Data

Time	Make	Model Sold
5:03	Honda	Accord
5:07	Honda	Civic
5:08	Honda	Civic
5:10	Toyota	Corolla
5:12	Toyota	Camry
5:14	Toyota	Corolla
5:50	Toyota	Corolla
6:03	Honda	Accord
6:07	Honda	Civic
6:08	Toyota	Corolla
6:10	Toyota	Camry
6:16	Toyota	Camry
6:28	Honda	Accord

Hourly Peak Volume 5:00 - 6:00

Time	Make	Model Sold	No of items Sold
5:00 - 6:00	Honda	Accord	1
5:00 - 6:00	Honda	Civic	2
5:00 - 6:00	Toyota	Corolla	3
5:00 - 6:00	Toyota	Camry	1

Hourly Peak Volume 6:00 - 7:00

Time	Make	Model Sold	No of items Sold
6:00 - 7:00	Honda	Accord	2
6:00 - 7:00	Honda	Civic	1
6:00 - 7:00	Toyota	Corolla	1
6:00 - 7:00	Toyota	Camry	2

Expected result

Max of peak hour volume. Max of 24 hrs data.

Time	Make	Model Sold	No of items Sold
6:00 - 7:00	Honda	Accord	2
5:00 - 6:00	Honda	Civic	2
5:00 - 6:00	Toyota	Corolla	3
6:00 - 7:00	Toyota	Camry	2

Can someone help me on this.

Re: Max of peak hour volume

strive — Sat, 23 Aug 2014 02:19:57 GMT

The Time column will contain only HH:MM data like this OR something else? What is the format of values in Time column?
How will you distinguish which days peak?

Re: Max of peak hour volume

th1agarajan — Sat, 23 Aug 2014 03:29:05 GMT

Actually there won't be any time in logs. It is just the indexed time. If i choose last 4 days then i should get only one result for each day for each model sold.i.e Peak hour sale of a day(24 hrs). Normally it happens during happy hours.

Re: Max of peak hour volume

strive — Sat, 23 Aug 2014 06:20:39 GMT

Try these

Per hour:

   Some search terms... | bucket _time span=1h | stats count(Model Sold) as Total_Sales by _time, Make, "Model Sold" | eventstats max(Total_Sales) as Max by _time, Make | where Total_Sales = Max | rename Total_Sales as "No of Models Sold" | fields - Max

Per day:

Some search terms... | bucket _time span=1d | stats count(Model Sold) as Total_Sales by _time, Make, "Model Sold" | eventstats max(Total_Sales) as Max by _time, Make | where Total_Sales = Max | rename Total_Sales as "No of Models Sold" | fields - Max

Updated:

Which hour had the max:

Some search terms... | bucket _time span=1h | stats count(Model Sold) as Total_Sales by _time, Make, "Model Sold" | eventstats max(Total_Sales) as Hour_Max by _time, Make | where Total_Sales = Hour_Max | fields - Hour_Max | bucket _time span=1d | eventstats max(Total_Sales) as Day_Max by _time, Make | where Total_Sales = Day_Max | rename Total_Sales as "No of Models Sold"

Second Update:

To get exact hour

Some search terms... | bucket _time span=1h | stats count(Model Sold) as Total_Sales by _time, Make, "Model Sold" | eventstats max(Total_Sales) as Hour_Max by _time, Make | where Total_Sales = Hour_Max | fields - Hour_Max | eval Hour_Of_Day = strftime(_time, "%H:%M") | bucket _time span=1d | eventstats max(Total_Sales) as Day_Max by _time, Make | where Total_Sales = Day_Max | rename Total_Sales as "No of Models Sold"

Re: Max of peak hour volume

th1agarajan — Mon, 28 Sep 2020 17:23:52 GMT

Thank you very much Strive. Hourly Peak Volume worked flawlessly but when I removed “eventstats max(Total_Sales) as Max by _time, Make” I was not able to see any difference.

My main requirement was Max of peak hour volume. i.e. Max of 24 hrs. data. But it didn’t work as I expected.

To make it clear. Based on the above table I was able to sell only 1 Accord from 5:00 – 6:00 but I was able to sell 2 Accords from 6:00 – 7:00. Then my result should be
6:00 – 7:00 Honda Accord 2

Assume if I sell 15 Accords from 7:00 to 8:00 and 12 Accords from 8:00 to 9:00 Then the result at 9:00 ‘o’ clock should be
7:00 – 8:00 Honda Accord 15

Per day:

If I use the above search I am getting sum of every one hour i.e.

This is what I am getting

Time	Make	Model Sold	No of items Sold
8/21/14 12:00:00.000 AM	Honda	Accord	3
8/21/14 12:00:00.000 AM	Honda	Civic	3
8/21/14 12:00:00.000 AM	Toyota	Corolla	4
8/21/14 12:00:00.000 AM	Toyota	Camry	3

But I need max of every one hour(Check the expected result above). It needs a slight fine tuning.

I was able to get the expected result using the below search

I introduced one more bucket. But in results in time column I am getting 8/21/14 12:00:00.000 AM. I need the exact hour when the sales was high. Can we have two different names for the bucket. I replaced the second _time with some different name but it didn't work.

Can you help me in getting the exact time as well in the result. Your help will be highly appreciated

Re: Max of peak hour volume

strive — Sun, 24 Aug 2014 07:21:54 GMT

So what you need is this.
First find out the max for every hour. For a day you will have 24 max values on per hour basis. Out of these you need to find out which hour had the max? Is that right?

Check my updated answer.

Re: Max of peak hour volume

th1agarajan — Sun, 24 Aug 2014 08:35:57 GMT

Exactly. Could you please respond to my comment for your updated answer?

Re: Max of peak hour volume

th1agarajan — Sun, 24 Aug 2014 08:43:53 GMT

You rock man. It worked flawlessly. But in results in time column I am getting 8/21/14 12:00:00.000 AM. I need the exact hour when the sales was high. Can we have two different names for the bucket. I replaced the second _time with some different name but it didn't work.
Can you help me in getting the exact time as well in the result. Your help will be highly appreciated.

Re: Max of peak hour volume

strive — Sun, 24 Aug 2014 08:46:17 GMT

Take the second update to get hour of day

Re: Max of peak hour volume

strive — Sun, 24 Aug 2014 08:46:25 GMT

Take the second update to get hour of day

Re: Max of peak hour volume

th1agarajan — Sun, 24 Aug 2014 09:32:05 GMT

I owe you a beer. It worked flawlessly. Thank you very much for helping me on a weekend.

Re: Max of peak hour volume

strive — Sun, 24 Aug 2014 12:55:12 GMT

🙂 Good to know that it worked. Happy Splunking 🙂 🙂

Re: Max of peak hour volume

sureshchinta — Tue, 12 May 2015 18:16:35 GMT

Lets say if the max value is 10 for three distinct hours in the day, the search returns all three hour values. Question is how to show the first occurrence ONLY of the three max values ?

Re: Max of peak hour volume

sureshchinta — Wed, 13 May 2015 14:37:41 GMT

Lets say if the max value is 10 for three distinct hours in the day, the search returns all three hour values. Question is how to show the first occurrence ONLY of the three max values ?

Re: Max of peak hour volume

sgundeti — Fri, 01 Jul 2016 14:53:25 GMT

use dedup on max value and time. It will show first value for each time span.

Re: Max of peak hour volume

Allampally — Mon, 29 Apr 2024 17:18:19 GMT

@strive , @th1agarajan - My requirement is similar to this but I don't want daily peak hour. I just need to get peak hour from time range. Lets say, If I am searching for last 7 days data, it needs to report only one peak hour of whole hours (out of 24*7) . How can I achieve this ?