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!

More Ways To Control Your Costs With Archived Metrics | Register for Tech Talk

Tuesday, May 14, 2024  |  11AM PT / 2PM ET Register to Attend Join us for this Tech Talk and learn how to ...

.conf24 | Personalize your .conf experience with Learning Paths!

Personalize your .conf24 Experience Learning paths allow you to level up your skill sets and dive deeper ...

Threat Hunting Unlocked: How to Uplevel Your Threat Hunting With the PEAK Framework ...

WATCH NOWAs AI starts tackling low level alerts, it's more critical than ever to uplevel your threat hunting ...