Getting Data In

Max of peak hour volume

Path Finder

I need the Max of peak hour volume

My Data

TimeMakeModel Sold
5:03HondaAccord
5:07HondaCivic
5:08HondaCivic
5:10ToyotaCorolla
5:12ToyotaCamry
5:14ToyotaCorolla
5:50ToyotaCorolla
6:03HondaAccord
6:07HondaCivic
6:08ToyotaCorolla
6:10ToyotaCamry
6:16ToyotaCamry
6:28HondaAccord

Hourly Peak Volume 5:00 - 6:00

TimeMakeModel SoldNo of items Sold
5:00 - 6:00HondaAccord1
5:00 - 6:00HondaCivic2
5:00 - 6:00ToyotaCorolla3
5:00 - 6:00ToyotaCamry1

Hourly Peak Volume 6:00 - 7:00

TimeMakeModel SoldNo of items Sold
6:00 - 7:00HondaAccord2
6:00 - 7:00HondaCivic1
6:00 - 7:00ToyotaCorolla1
6:00 - 7:00ToyotaCamry2

Expected result

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







TimeMakeModel SoldNo of items Sold
6:00 - 7:00HondaAccord2
5:00 - 6:00HondaCivic2
5:00 - 6:00ToyotaCorolla3
6:00 - 7:00ToyotaCamry2

Can someone help me on this.

0 Karma
1 Solution

Influencer

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"

View solution in original post

Explorer

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 ?

0 Karma

Path Finder

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







TimeMakeModel SoldNo of items Sold
8/21/14 12:00:00.000 AMHondaAccord3
8/21/14 12:00:00.000 AMHondaCivic3
8/21/14 12:00:00.000 AMToyotaCorolla4
8/21/14 12:00:00.000 AMToyotaCamry3

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

0 Karma

Influencer

Take the second update to get hour of day

0 Karma

Explorer

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 ?

0 Karma

Path Finder

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

0 Karma

Path Finder

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

0 Karma

Influencer

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.

0 Karma

Influencer

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"

View solution in original post

Influencer

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

0 Karma

Path Finder

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

0 Karma

Influencer

Take the second update to get hour of day

0 Karma

Path Finder

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.

0 Karma

Path Finder

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.

0 Karma

Influencer

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?

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!