Splunk Search

Sort result of bin to draw distribution histogram

BernardEAI
Communicator

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-027531
0-100000151267
100000-20000014649
1000000-1100000361
1100000-1200000371
1200000-1300000197
1300000-1400000119
1400000-150000070
1500000-160000064
1600000-1700000111
1700000-180000076
1800000-190000069
1900000-200000027
200000-3000008390
2000000-210000020
2100000-220000022
2200000-230000012
2300000-240000010
2400000-25000008
Labels (1)
0 Karma
1 Solution

BernardEAI
Communicator

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

 

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma

BernardEAI
Communicator

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

 

View solution in original post

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!