Splunk Search

How to search the item name with max number of items sold per hour?

th1agarajan
Path Finder

I need the item name and no of items sold based on max(itemSold) per hour

TimeItemNo Of ItemsSold
5:02xxx5
5:05yyy25
5:07zzz500
6:03yyy200
6:07zzz100
6:28xxx230

Expected result






TimeItemNo Of ItemsSold
5:00zzz500
6:00xxx230

MySearch


index=mystore* sourcetype=mystore source=mystore-APP host="mystore7540" | bucket span=1h _time | stats max(itemSold) by _time | sort _time

The above search works perfectly but i am getting only two fields in result i.e. Time and max(itemsSold). But i need the item name as well.

It tried something like this but it didn't work

index=mystore* sourcetype=mystore source=mystore-APP host="mystore7540" | bucket span=1h _time | stats max(itemSold) by _time | sort _time | fields item

Can someone help me on this.

Tags (4)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Your approach with bucket | stats is correct, it just lacks one more grouping field and a bit of postprocessing:

  base search | bucket span=1h _time | stats sum(itemSold) as sum by _time item
| eventstats max(sum) as max by _time | where max==sum | fields - max

Note, in the event of two items having the same sum per hour you will get two entries for that hour.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

Your approach with bucket | stats is correct, it just lacks one more grouping field and a bit of postprocessing:

  base search | bucket span=1h _time | stats sum(itemSold) as sum by _time item
| eventstats max(sum) as max by _time | where max==sum | fields - max

Note, in the event of two items having the same sum per hour you will get two entries for that hour.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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