Renaming or ordering values used in a stats by query

[jrs42]

Here's a part of my query, ignoring where the data is coming from:

| eval bucket=case(dur < 30, "Less than 30sec", dur <= 60, "30sec - 60sec", dur <= 120, "1min - 2min", dur <= 240, "2min - 4min", dur > 240, "More than 4min")
| eval sort_field=case(bucket="Less than 30sec", 1, bucket="30sec - 60sec", 2, bucket="1min - 2min", 3, bucket="2min - 4min", 4, bucket="More than 4min", 5)
| sort sort_field
| stats count as "Number of Queries" by bucket

The problem I have is that the results are ordered alphabetically by the name of each bucket.  I'd prefer to have the order always be from quickest to slowest: <30s, 30-60s, 1-2m, 2-4m, >4m

What I get:

1min - 2min |  <value>
2min - 4min |  <value>
30sec - 60sec |  <value>
Less than 30sec |  <value>
More than 4min |  <value>

What I want:

Less than 30sec |  <value>
30sec - 60sec |  <value>
1min - 2min |  <value>
2min - 4min |  <value>
More than 4min |  <value>

I've tried a number of different approaches, none seeming to do anything.  Is this possible?

[ITWhisperer · Accepted solution]

| eval bucket=case(dur < 30, "Less than 30sec", dur <= 60, "30sec - 60sec", dur <= 120, "1min - 2min", dur <= 240, "2min - 4min", dur > 240, "More than 4min")
| eval sort_field=case(bucket="Less than 30sec", 1, bucket="30sec - 60sec", 2, bucket="1min - 2min", 3, bucket="2min - 4min", 4, bucket="More than 4min", 5)
| stats count as "Number of Queries" by bucket sort_field
| sort sort_field
| fields - sort_field

[jrs42]

Extra credit:

Is there a way to force all 5 buckets to always appear in the results, even if they have a 0 count?

[ITWhisperer]

Try something like this

| eval bucket=case(dur < 30, 0, dur <= 60, 1, dur <= 120, 2, dur <= 240, 3, dur > 240, 4)
| stats count as "Number of Queries" by bucket
| append
  [| makeresults
  | fields - _time
  | eval bucket=mvrange(0,5)
  | mvexpand bucket
  | eval "Number of Queries"=0]
| stats sum('Number of Queries') as "Number of Queries" by bucket
| eval bucket=mvindex(split("Less than 30sec,30sec - 60sec,1min - 2min,2min - 4min,More than 4min", ","), bucket)

[jrs42]

Thank you!  I was so close lol.

I hacked it by prepending "  " and " " to a couple of bucket names to force them to sort ahead, but that made me cringe.  This is far better.  Thanks again!