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

[tonahoyos]

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?

[tonahoyos]

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!

[DalJeanis]

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

[lfedak_splunk]

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

[justinatpnnl]

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

[tonahoyos]

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?

[tonahoyos]

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

[justinatpnnl]

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)

[justinatpnnl]

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.

[MuS · Accepted solution]

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

[tonahoyos]

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,

[tonahoyos]

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

[MuS]

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