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.

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

View solution in original post

0 Karma

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

View solution in original post

0 Karma

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

0 Karma

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

0 Karma

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

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!