Splunk Search

How can I group last results into OTHERS?

anoopk1981
New Member

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

Tags (1)
0 Karma

Vijeta
Influencer

Try this

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| rename "Destination Country Correct Translation" as Country

| 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

0 Karma

anoopk1981
New Member

Yes. That worked. Than You

0 Karma

Vijeta
Influencer

Please accept the answer if that worked for you. 🙂

0 Karma

dstile
Explorer

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

0 Karma

anoopk1981
New Member

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?

0 Karma

dstile
Explorer

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

0 Karma

anoopk1981
New Member

I tried but got this result

Destination Country Correct Translation sales Percentage total_sales
Others 1855 100 1855

0 Karma

dstile
Explorer

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

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

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!

0 Karma

Vijeta
Influencer

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

0 Karma

anoopk1981
New Member

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

0 Karma

Vijeta
Influencer

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

0 Karma

anoopk1981
New Member

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

0 Karma

Vijeta
Influencer

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.

0 Karma

Vijeta
Influencer

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

|rename “Destination Country Correct Translation" as Country

0 Karma

anoopk1981
New Member

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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
0 Karma

anoopk1981
New Member

Thank You.

0 Karma

anoopk1981
New Member

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

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...