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 |
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 |
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.
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"
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 ?
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:
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
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
Some search terms... | bucket _time span=1h | stats count(Model Sold) as Total_Sales by _time, Make, "Model Sold" | bucket _time span=1d | eventstats max(Total_Sales) as Max by _time, Make | where Total_Sales = Max | rename Total_Sales as "No of Models Sold" | fields – Max
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
Take the second update to get hour of day
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 ?
use dedup on max value and time. It will show first value for each time span.
Exactly. Could you please respond to my comment for your updated answer?
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.
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"
🙂 Good to know that it worked. Happy Splunking 🙂 🙂
I owe you a beer. It worked flawlessly. Thank you very much for helping me on a weekend.
Take the second update to get hour of day
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.
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.
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?