Splunk Search

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

New Member

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.

Tags (5)
1 Solution
Motivator

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!!

Motivator

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!!

New Member

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

Motivator

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.!!!!!

SplunkTrust

@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
``````
New Member

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.