Getting Data In

Max of peak hour volume

th1agarajan
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

strive
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

sureshchinta
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

th1agarajan
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

strive
Influencer

Take the second update to get hour of day

0 Karma

sureshchinta
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

sgundeti
Path Finder

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

0 Karma

th1agarajan
Path Finder

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

0 Karma

strive
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

strive
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"

strive
Influencer

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

0 Karma

th1agarajan
Path Finder

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

0 Karma

strive
Influencer

Take the second update to get hour of day

0 Karma

th1agarajan
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

th1agarajan
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

strive
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
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...