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, Karma would be appreciated.

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

 

0 Karma

romedome
Path Finder

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

 

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...