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

[th1agarajan]

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

Time	Item	No Of ItemsSold
5:02	xxx	5
5:05	yyy	25
5:07	zzz	500
6:03	yyy	200
6:07	zzz	100
6:28	xxx	230

Expected result

Time	Item	No Of ItemsSold
5:00	zzz	500
6:00	xxx	230

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.

[martin_mueller · Accepted solution]

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.