Hi,
I have a search that gives me results as below
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
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
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
Yes. That worked. Than You
Please accept the answer if that worked for you. 🙂
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
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?
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
I tried but got this result
Destination Country Correct Translation sales Percentage total_sales
Others 1855 100 1855
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
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!
You can use at the end of your query- |top limit=10 Percentage useother=1
top won't work. top gives me the values by the most number of occurrences . This is entirely different .
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
It gave me only the Other row..not the first five rows + Other .
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.
You need to add this command after your query and before streamstats
|rename “Destination Country Correct Translation" as Country
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
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
Thank You.
I tried it but in the result it shows only the first 5 rows..
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