topic Re: How do I divide the sum based on a criterion using the eval command? in Splunk Search

How do I divide the sum based on a criterion using the eval command?

xelian — Sat, 06 Oct 2018 08:51:42 GMT

Hi I have the following search:

[my search]
|dedup @timestamp 
|stats sum(json_message.amount) as "total" by json_message.currencyCode 
|eval devide = if(like(json_message.currencyCode,"%IDR%") , "1748000" , "100")
|eval final=total/devide 
|table json_message.currencyCode final
|rename final as "Total in real amount",  json_message.currencyCode as "Currency"

In the result table, I want the total amount to be divided by 1748000 for IDR and by 100 for other currencies.

Re: How do I divide the sum based on a criterion using the eval command?

renjith_nair — Sat, 06 Oct 2018 13:16:53 GMT

@xelian ,

Your search should work. Are you not getting the result? Suggest to remove the quotes around the numbers in the condition though it should not affect your result.

This is a run anywhere example

| makeresults |eval currencies="IDR,EUR,USD,AUD"|makemv currencies delim=","| mvexpand currencies|eval total=100
|eval divider=if(currencies=="IDR",100,10)|eval final=total/divider|fields - divider

Re: How do I divide the sum based on a criterion using the eval command?

xelian — Sat, 06 Oct 2018 17:40:53 GMT

I remove quotes but in the result always 100 is devide. I think the problem is somewhere in the if, because it is a String. I do not know I am very upset. I tried 100 different scenarios and always 100.

Re: How do I divide the sum based on a criterion using the eval command?

VatsalJagani — Sat, 06 Oct 2018 18:58:19 GMT

Hi @xelian,
Can you please try below query:

[my search]
|dedup @timestamp 
| rename json_message.currencyCode as currencyCode,  json_message.amount as amount
|stats sum(amount) as "total" by currencyCode 
|eval devide = if(like(currencyCode,"%IDR%") , 1748000 , 100)
|eval final=total/devide 
|table currencyCode final
|rename final as "Total in real amount",  currencyCode as "Currency"

Hope this helps!!

Re: How do I divide the sum based on a criterion using the eval command?

xelian — Sat, 06 Oct 2018 19:50:53 GMT

WoW man, rename must be first. OOOOO I read half of the SPlunk doc for if/case/string/toNumber tried 100 different scnearios. Thankssssss

Re: How do I divide the sum based on a criterion using the eval command?

VatsalJagani — Sat, 06 Oct 2018 20:31:16 GMT

Yes, you caught that correctly, always consider renaming the field name if field name cannot be a variable name for your script, for being on safe side.!!!!!