Splunk Search
Highlighted

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

Explorer

I have the following search:

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

and the following second search:

index="dataintegration" host="sampledata" sourcetype="csv" ObjectAccount="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?

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma
Highlighted

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

Explorer

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,

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

Highlighted

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

Communicator

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

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

Explorer

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 objectaccount contains a 4%, then it will add the DomesticAmount values, else it turns the value Sales to 0?

0 Karma
Highlighted

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

Communicator

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.

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Communicator

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)

0 Karma