topic Re: How can I run stats sum as command on same search for two different values? in Splunk Search

How can I run stats sum as command on same search for two different values?

tonahoyos — Tue, 29 Sep 2020 16:07:55 GMT

I have the following search:

index="data_integration" host="sampledata" sourcetype="csv" Object_Account="4*" OR Object_Account="5*"|stats sum("Domestic _Amount") AS CM

and the following second search:

index="data_integration" host="sampledata" sourcetype="csv" Object_Account="4*"| stats sum("Domestic _Amount") AS Sales

I want to be able to divide CM/Sales. What is the best command or the best way to join these two searches in order to do the division?

Re: How can I run stats sum as command on same search for two different values?

MuS — Mon, 09 Oct 2017 21:36:59 GMT

Hi tonahoyos,

you can try something like this:

source="Dataset_Finance.csv" host="sample" index="dataintegration" sourcetype="SampleFinance" ObjectAccount="4*" OR ObjectAccount="5*"
| eval Sales=if(ObjectAccount="411010",DomesticAmount,0), Costs=if(like(ObjectAccount, "5%"),DomesticAmount,0)
| stats sum(Sales) as Sales, sum(Costs) as Costs 
| eval CM=Sales+Costs 
| eval CMPer=CM/Sales

The first eval checks the Object_Account and sets the value used, this value is used in the stats to sum the total number.
This is untested, so please bear in mind that you might need to adapt this to your actual events, but it should help you to get you going.

cheers, MuS

Re: How can I run stats sum as command on same search for two different values?

justinatpnnl — Mon, 09 Oct 2017 23:38:01 GMT

Here is another way to do this with a single stats command using an inline eval:

index="data_integration" host="sampledata" sourcetype="csv" Object_Account="4*" OR Object_Account="5*"
| stats sum("Domestic _Amount") as CM, sum( eval( if( Object_Account like "4%", 'Domestic _Amount', 0 ))) as Sales
| eval ratio = Sales / CM

Re: How can I run stats sum as command on same search for two different values?

lfedak_splunk — Tue, 10 Oct 2017 00:27:47 GMT

Hey @tonahoyos, if they solved your problem, remember to "√Accept" an answer to award karma points 🙂

Re: How can I run stats sum as command on same search for two different values?

tonahoyos — Tue, 29 Sep 2020 16:13:43 GMT

Hello justinatpnnl,

First, I appreciate your help! I have tested the search and it seems as if there is an issue with the following:

sum( eval( if( Object_Account like "4%", 'Domestic _Amount', 0 ))) as Sales

The CM amount shows up, but the Sales amount never does. I have played with it and moved it around a little bit, but I have not been able to sum the Object_Account="4*" amounts.

Also, I have a question about the if statement. Does it mean that if the object_account contains a 4%, then it will add the Domestic_Amount values, else it turns the value Sales to 0?

Re: How can I run stats sum as command on same search for two different values?

tonahoyos — Wed, 11 Oct 2017 18:37:05 GMT

Hello MuS,

Thank you for your answer and your time! I have not been able to solve the search. It seems as if there was an issue after the first eval statement. It narrows down the search to the 4* and 5* Object_account numbers, but it does not follow the first eval section correctly.

Thank you for your help, I will keep on working on it.

Best,

Re: How can I run stats sum as command on same search for two different values?

justinatpnnl — Wed, 11 Oct 2017 18:37:56 GMT

My guess is that Splunk is having trouble with the field that has a space in it. Can you try first renaming that field to something simpler and see if it works? I used the single quotes around Domestic _Amount to hopefully prevent that issue, but I'm wondering if that is the hang up.

For your second question, it is doing a sum on the values returned from the if statement. If it finds a value of 4% (% being a wildcard when using LIKE), then it returns the value of the Domestic _Amount field. Otherwise it returns a zero, so that it doesn't get added to sales.

Re: How can I run stats sum as command on same search for two different values?

DalJeanis — Wed, 11 Oct 2017 19:10:02 GMT

Is there really a space in "Domestic _Amount"? I can't imagine why someone would name a field that.

Re: How can I run stats sum as command on same search for two different values?

tonahoyos — Tue, 29 Sep 2020 16:10:27 GMT

Hello everyone,

I tried a different way to solve the problem. I am getting close, but my Cost ("5*") do not sum, the if statement throws out the 0 instead. What can I do to fix this? Should I try a different command?

source="Dataset_Finance.csv" host="sample" index="dataintegration" sourcetype="SampleFinance" ObjectAccount="4*" OR ObjectAccount="5*"
| stats sum(eval(if(ObjectAccount="411010",DomesticAmount,0))) as Sales, sum(eval(if(ObjectAccount="5*",DomesticAmount,0))) as Costs
| eval CM=Sales+Costs
| eval CMPer=CM/Sales

Thank you for all the help!

Re: How can I run stats sum as command on same search for two different values?

tonahoyos — Tue, 29 Sep 2020 16:10:30 GMT

I did the search a different way, but it seems as if the second eval statement is just throwing out the 0 instead of doing the sum, do you see anything wrong with this search?

source="Dataset_Finance.csv" host="sample" index="dataintegration" sourcetype="SampleFinance" ObjectAccount="4*" OR ObjectAccount="5*"
| stats sum(eval(if(ObjectAccount="411010",DomesticAmount,0))) as Sales, sum(eval(if(ObjectAccount="5*",DomesticAmount,0))) as Costs
| eval CM=Sales+Costs
| eval CMPer=CM/Sales

Re: How can I run stats sum as command on same search for two different values?

tonahoyos — Tue, 29 Sep 2020 16:10:32 GMT

I did the search a different way, but it seems as if the second eval statement is just throwing out the 0 instead of doing the sum, do you see anything wrong with this search?

source="Dataset_Finance.csv" host="sample" index="dataintegration" sourcetype="SampleFinance" ObjectAccount="4*" OR ObjectAccount="5*"
| stats sum(eval(if(ObjectAccount="411010",DomesticAmount,0))) as Sales, sum(eval(if(ObjectAccount="5*",DomesticAmount,0))) as Costs
| eval CM=Sales+Costs
| eval CMPer=CM/Sales

Re: How can I run stats sum as command on same search for two different values?

MuS — Tue, 29 Sep 2020 16:15:14 GMT

This is because you use a 5* in the if statement, that does not work. You have to add a like() (pro tip: for easier reading and understanding move eval out of stats - no performance impact at all 😉 😞

source="Dataset_Finance.csv" host="sample" index="dataintegration" sourcetype="SampleFinance" ObjectAccount="4*" OR ObjectAccount="5*"
| eval Sales=if(ObjectAccount="411010",DomesticAmount,0), Costs=if(like(ObjectAccount, "5%"),DomesticAmount,0)
| stats sum(Sales) as Sales, sum(Costs) as Costs
| eval CM=Sales+Costs
| eval CMPer=CM/Sales

cheers, MuS

Re: How can I run stats sum as command on same search for two different values?

justinatpnnl — Fri, 13 Oct 2017 20:42:58 GMT

Within an if statement, you can't use * as a wildcard. You have to use LIKE with % as the wildcard.

if(ObjectAccount LIKE "5%",DomesticAmount, 0)