How to find the field value corresponding to an extremity (min, max)?

[yuanliu]

Say I have sales figures

  Month   Sales
  June      44
  July      55
  August    66
  September 60
  November  50

How do I know that August is the month when sales maximized? This seems to be a common operation, but I can't find an easy function to call.

All I can come up with is a convoluted manipulation, like

| eventstats max(Sales) as maxSales
| eval maxMonth=if(Sales==maxSales,Month,null)
| stats max(maxSales) values(maxMonth)

(It is OK if two field values match.)

As this is just a small part of a more complex operation, it feels awkward to do all these just to find peak value of an associated field.

[yuanliu · Accepted solution]

Now that I'm more comfortable with SPL, I can think of a few more alternatives.  This one is the cleanest yet

| eventstats max(eval(printf("%06d", Sales) . ":" . Month)) as sales_max
| eval maxMonth = replace(sales_max, "^\d+:", "")

The advantage here is that every event gets maxMonth without doing another stats operation. Caveat: If there are multiple maxima in the numeric field, it will pick the one with the highest ASCII value of the other field. (In most applications this is acceptable.)

[woodcock]

The easiest way is:

... | sort - Sales | head 1

But you probably need all the events so try:

... | eventstats max(Sales) as maxSales

And then maybe after that:

... | where Sales=maxSales | table Month

Or:

... | eval MaxSalesMonth = if((Sales==maxSales), "Yes", "No")

[yuanliu]

Yes, I am looking to keep all events, so head and where do not apply. the last one is the same as the one I posted in the question, which I feel convoluted, as the number of events involved are numerous. I need the associated value(s) only as a quick reference in a complex scheduled search that already uses lots of stats manipulations.

Of course, if there is no better way, I'll have to take what I get.