Sort result of bin to draw distribution histogram

[BernardEAI]

Hi

I'm trying to draw a distribution histogram of the duration to complete a specific action. The search is:

index=index1 STATUS=Executed
| eval EXECUTED_DATE_e = strptime(EXECUTED_DATE, "%Y-%m-%d %H:%M:%S.%1N") 
| eval START_DATE_e = strptime(START_DATE, "%Y-%m-%d %H:%M:%S.%1N") 
| eval TTR = EXECUTED_DATE_e - START_DATE_e 
| bin bins=100 TTR
| stats count by TTR

This produces the correct bins and counts, but the order is alphanumeric, which places 1000000-2000000 directly after 100000-200000, instead of 200000-300000. If I plot this result the bins are in the wrong location, and I cannot clearly interpret the distribution histogram.

-100000-0	27531
0-100000	151267
100000-200000	14649
1000000-1100000	361
1100000-1200000	371
1200000-1300000	197
1300000-1400000	119
1400000-1500000	70
1500000-1600000	64
1600000-1700000	111
1700000-1800000	76
1800000-1900000	69
1900000-2000000	27
200000-300000	8390
2000000-2100000	20
2100000-2200000	22
2200000-2300000	12
2300000-2400000	10
2400000-2500000	8

[richgalloway]

The bin labels are strings rather than integers so they sort lexicographically.  Try this alternative

... | stats count, first(START_DATE_e) as start by TTR
| sort + start
| fields - start

[BernardEAI · Accepted solution]

Thanks @richgalloway 

I tried this solution, but it didn't work. I eventually solved this by making a kvstore from a csv file, in which I defined the bins and their order. I could then get an order for each TTR bin, and then sort according to that order. For this to work you need to define the span and stick to it, otherwise the bin string changes, and then the lookup doesn't work anymore.

Therefore:

bin span=1 TTR

rather than:

bin bins=400 TTR

[romedome]

richgalloway's solution works, it was just missing one line of code. Try something like this:

| bin indexlag as bin bins=100
| stats min(indexlag) as starting_value count BY bin
| sort starting_value
| fields - starting_value