topic Re: How can I group last results into OTHERS? in Splunk Search

How can I group last results into OTHERS?

anoopk1981 — Mon, 08 Oct 2018 21:46:19 GMT

Hi,

I have a search that gives me results as below

"Country" "Sales" "Total Sales" "Percentage"

 A                  300         1855           16.5
 B                  250         1855           13.2
 C                  200         1855           10.9
 D          190         1855           10.2
 E                  180         1855            9.7
 F                  160         1855            8.6
 G          155         1855            8.3
 H          145         1855            7.8
 I                   140    1855            7.5
 J                   135    1855            7.2

Now I want to show only the first 5 rows and group the remaining rows into Others..something as below

"Country" "Sales" "Total Sales" "Percentage"

 A                  300         1855           16.5
 B                  250         1855           13.2
 C                  200         1855           10.9
 D          190         1855           10.2
 E                  180         1855            9.7

OTHERS 735 1855 39.6

Thank You

Re: How can I group last results into OTHERS?

DalJeanis — Tue, 09 Oct 2018 02:58:45 GMT

Here's one way ...

your search
| sort 0 - Percentage
| streamstats count as recno
| appendpipe 
    [ | where recno > 5 
      | stats sum(Sales) as Sales sum(Percentage) as Percentage by "Total Sales" 
      | eval Country = "OTHER" 
      | eval recno=5.5 
     ]
| where recno <6
| fields - recno

Re: How can I group last results into OTHERS?

anoopk1981 — Tue, 09 Oct 2018 20:44:48 GMT

Thank You.

Re: How can I group last results into OTHERS?

anoopk1981 — Tue, 09 Oct 2018 20:53:25 GMT

I tried it but in the result it shows only the first 5 rows..

"Country" "Sales" "Total Sales" "Percentage"

A 300 1855 16.5
B 250 1855 13.2
C 200 1855 10.9
D 190 1855 10.2
E 180 1855 9.7

Re: How can I group last results into OTHERS?

Vijeta — Tue, 09 Oct 2018 21:07:57 GMT

You can use at the end of your query- |top limit=10 Percentage useother=1

Re: How can I group last results into OTHERS?

mstjohn_splunk — Tue, 09 Oct 2018 22:43:55 GMT

hi @anoopk1981

Did the answer below solve your problem? If so, please resolve this post by approving it!
If your problem is still not solved, keep us updated so that someone else can help ya. Thanks for posting!

Re: How can I group last results into OTHERS?

anoopk1981 — Tue, 09 Oct 2018 23:48:14 GMT

top won't work. top gives me the values by the most number of occurrences . This is entirely different .

Re: How can I group last results into OTHERS?

Vijeta — Wed, 10 Oct 2018 01:59:07 GMT

Try this

your search
| sort 0 - Percentage
|streamstats count as c
| eval Country=if(c>5,"Other",Country)
| stats sum(Sales) as Sales sum(Percentage) as Percentage sum("Total Sales") as "Total Sales" by Country

Re: How can I group last results into OTHERS?

dstile — Wed, 10 Oct 2018 02:18:32 GMT

Put an eval like this before the transform command that gives you the Sales by Country:

eval Country = case(Country=="A","A",Country=="B","B",Country=="C","C",Country=="D","D",Country=="E","E",true(),"Others")

for example:

index="myindex"
| eval Country = case(Country=="A","A",Country=="B","B",Country=="C","C",Country=="D","D",Country=="E","E",true(),"Others")
| stats count as Sales by Country
| eventstats sum(Sales) as TotalSales
| eval Percentage = Sales / TotalSales

Re: How can I group last results into OTHERS?

anoopk1981 — Tue, 29 Sep 2020 21:35:42 GMT

This is my main search

index = "myindex" | eventstats sum("Declared Value _USD") as total_sales |stats sum("Declared Value _USD") as sales max(total_sales) as total_sales by "Destination Country Correct Translation"|eval Percentage=sales/total_sales*100 |sort 0 - sales

So where do you suggest to put your statements?

Re: How can I group last results into OTHERS?

anoopk1981 — Wed, 10 Oct 2018 19:08:34 GMT

It gave me only the Other row..not the first five rows + Other .

Re: How can I group last results into OTHERS?

Vijeta — Wed, 10 Oct 2018 19:15:56 GMT

Please check the value of c generated using streamstats. The value should be 1,2,3,4,5,6,....
Also please make sure the fieldname from your query is same as the fieldname used for Country . It will be helpful if you can paste your code here.

Re: How can I group last results into OTHERS?

dstile — Tue, 29 Sep 2020 21:32:40 GMT

index = "myindex"
| eval "Destination Country Correct Translation" = case("Destination Country Correct Translation"=="A","A","Destination Country Correct Translation"=="B","B","Destination Country Correct Translation"=="C","C","Destination Country Correct Translation"=="D","D","Destination Country Correct Translation"=="E","E",true(),"Others")
| stats sum("Declared Value _USD") as sales by "Destination Country Correct Translation"
| eventstats sum(sales) as total_sales
| eval Percentage=sales/total_sales*100
| sort 0 - sales

Re: How can I group last results into OTHERS?

Vijeta — Thu, 11 Oct 2018 02:30:33 GMT

You need to add this command after your query and before streamstats

|rename “Destination Country Correct Translation" as Country

Re: How can I group last results into OTHERS?

anoopk1981 — Thu, 11 Oct 2018 19:20:34 GMT

I tried but got this result

Destination Country Correct Translation sales Percentage total_sales
Others 1855 100 1855

Re: How can I group last results into OTHERS?

anoopk1981 — Tue, 29 Sep 2020 21:36:24 GMT

This is my current query..

index="marketing1" sourcetype="csv" | eventstats sum("Declared Value _USD") as total_sales |stats sum("Declared Value _USD") as sales max(total_sales) as total_sales by "Destination Country Correct Translation"|eval Percentage=sales/total_sales*100 |sort 0 - sales

Re: How can I group last results into OTHERS?

Vijeta — Tue, 29 Sep 2020 21:33:23 GMT

Try this

| sort 0 - Percentage
|streamstats count as c
| eval Country=if(c>5,"Other",Country)
| stats sum(sales) as Sales sum(Percentage) as Percentage max("total_sales") as "Total Sales" by Country

Re: How can I group last results into OTHERS?

anoopk1981 — Fri, 12 Oct 2018 16:32:03 GMT

Yes. That worked. Than You

Re: How can I group last results into OTHERS?

Vijeta — Fri, 12 Oct 2018 16:48:26 GMT

Please accept the answer if that worked for you. 🙂

Re: How can I group last results into OTHERS?

dstile — Tue, 29 Sep 2020 21:40:56 GMT

I think you got bit by an issue Splunk has with using fields that include spaces in evals. A rename of the Destination Country Correct Translation to a field name with no spaces would have sorted it.

index = "myindex"
| rename "Destination Country Correct Translation" as Country
| eval Country = case(Country=="A","A",Country=="B","B",Country=="C","C",Country=="D","D",Country=="E","E",true(),"Others")
| stats sum("Declared Value _USD") as sales by Country
| eventstats sum(sales) as total_sales
| eval Percentage=sales/total_sales*100
| sort 0 - sales