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 |
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
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
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
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