how to order a result by the sequence of the terms I've searched?

[rafasalo]

Hello,

I have the following query:

Index=A
BALL
SQUARE
TRIANGLE | stats count by Keyword

The result I get is something like:

TRIANGLE 100
BALL 50
SQUARE 8

I would like to have the result in the same order I've searched. So, I'm expecting this:
BALL 50
SQUARE 8
TRIANGLE 100

Help me, if anybody have any idea.

Thanks in advance.

[somesoni2 · Accepted solution]

Try like this. You'd need to update the keyword string in both subsearch.

index=indexA [| gentimes start=-1 | eval search="BALL SQUARE TRIANGLE" | table search| makemv search | mvexpand search] | stats count by Keyword | append [| gentimes start=-1 | eval Keyword="BALL SQUARE TRIANGLE" | table  Keyword | makemv Keyword | mvexpand Keyword | eval temp=1 count=0 ] | eventstats max(count) as count by Keyword | where temp=1 | table Keyword count

[rafasalo]

I think I was not clear.
This is my query:

index=index_cbo_pt [search index=index_cbo ("Scope State: 65280" 3.1.4.0) OR ("Scope State: 65280" 3.1.4.1)| stats count by CNPJ | sort - count | table CNPJ]| stats count by CNPJ

This is what I've got:

CNPJ                              COUNT
910191019                         450
272817181                         300
818191819                         250

However what I want is a result ordered by the order of my subsearch.

My subsearch results on this:

CNPJ                              
272817181 
910191019                  
818191819

So, what I need is this:

CNPJ                              COUNT
272817181                         300
910191019                         450
818191819                         250

[somesoni2]

Give this a try

index=index_cbo_pt [search index=index_cbo ("Scope State: 65280" 3.1.4.0) OR ("Scope State: 65280" 3.1.4.1)| stats count by CNPJ | sort - count | table CNPJ]| stats count by CNPJ | append [search index=index_cbo ("Scope State: 65280" 3.1.4.0) OR ("Scope State: 65280" 3.1.4.1)| stats count by CNPJ | sort - count | table CNPJ | eval temp=1 | eval count=0 ] | eventstats max(count) as count by CNPJ | where temp=1 | table CNPJ count

[rafasalo]

Great!! Thank you very much!
cheers,

[somesoni2]

How are you getting 3 rows after just executing | stats count?

[rafasalo]

Bellow I explained better. thanks!

[vasanthmss]

syntax for the sort is sort <count> <sort by class>

something like this index=blah | stats count by field1 | sort 0 field1 Here field1 has the values of your ball, square etc. If you need to sort by ascending you need to use | sort 0 field1 for decending | sort 0 -field1.

Note: If no number is specified for count, the default limit of 10000 is used. If the number 0 is specified, all results are returned

Hope this will help you.

[rafasalo]

I think I was not clear.
This is my query:

index=index_cbo_pt [search index=index_cbo ("Scope State: 65280" 3.1.4.0) OR ("Scope State: 65280" 3.1.4.1)| stats count by CNPJ | sort - count | table CNPJ]| stats count by CNPJ

This is what I've got:

CNPJ                              COUNT
910191019                         450
272817181                         300
818191819                         250

However what I want is a result ordered by the order of my subsearch.

My subsearch results on this:

CNPJ                              
272817181 
910191019                  
818191819

So, what I need is this:

CNPJ                              COUNT
272817181                         300
910191019                         450
818191819                         250

[sundareshr]

You search query as you have here, will return only one value. Count of events. I think you are missing a by clause. | stats count by keyword? Assuming it is keyword, you can use the sort command to sort your results

[rafasalo]

The problem is that my keyword is something like a Social Number, so, if I sort, splunk orders considering it a number.

[rafasalo]

You are right. I've edited my query above.