Splunk Search

show data as in the order defined in query

dsiob
Communicator

I have a chart shows counts of Policies under different Policy Amount ranges (eg: 10000-50000).

Query:
index|rename Amount as a |eval range = case (a>=10000 and a <= 20000, "10 - 20k" , a>20000 and a <= 50000, "20 - 50k" ,a>50000 and a <= 100000, "50 - 100k" ,a>100000 and a <= 500000, "100 - 500k" ,a>=500000 , ">500k" )| stats count(Policies) by range

It shows y axis data in chart in order as:

10-20k

100-500k

20-50k
50-100k
500k

It is taking ascending order and re ordering.
But I want in the order I defined the ranges, otherwise chart will become irrelevent.

I want it as :
10-20k
20-50k
50-100k
100-500k
500k

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this (appending serial number will ensure properly sorted output after stats, last eval is to remove those prefix sno)

index|rename Amount as a |eval range = case (a>=10000 and a <= 20000, "1. 10 - 20k" , a>20000 and a <= 50000, "2. 20 - 50k" ,a>50000 and a <= 100000, "3. 50 - 100k" ,a>100000 and a <= 500000, "4. 100 - 500k" ,a>=500000 , "5. >500k" )| stats count(Policies) by range
| eval range=substr(range,4)

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try like this (appending serial number will ensure properly sorted output after stats, last eval is to remove those prefix sno)

index|rename Amount as a |eval range = case (a>=10000 and a <= 20000, "1. 10 - 20k" , a>20000 and a <= 50000, "2. 20 - 50k" ,a>50000 and a <= 100000, "3. 50 - 100k" ,a>100000 and a <= 500000, "4. 100 - 500k" ,a>=500000 , "5. >500k" )| stats count(Policies) by range
| eval range=substr(range,4)
0 Karma

dsiob
Communicator

its working! thanks somesoni2

0 Karma

sbbadri
Motivator

try this,

your base query | table "10-20k", "20-50k","50-100k","100-500k","500k"

0 Karma

dsiob
Communicator

"10-20k", "20-50k","50-100k","100-500k","500k" are not colums, they are column values

0 Karma

sbbadri
Motivator

Post the sample table. Did you try with the query. Because your are group by range.

0 Karma

dsiob
Communicator
source="Agent_Details_4.csv" host="397AD-1A210036" sourcetype="csv" | rename "FACE AMOUNT" as p | eval range=case(p>=0 AND p<=100000, "0-100 K", p>100000 AND p<=200000, "100-200 K",
  p>20000 AND p<=50000, "200-500 K",p>500000 AND p<=1000000, "500-1 M",p>1000000 ,  ">1M")|stats count as "Policy count" by range
0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...