Splunk Search

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

tonahoyos
Explorer

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?

0 Karma
1 Solution

MuS
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

tonahoyos
Explorer

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!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

lfedak_splunk
Splunk Employee
Splunk Employee

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

0 Karma

justinatpnnl
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

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

0 Karma

tonahoyos
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

justinatpnnl
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

justinatpnnl
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

MuS
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

0 Karma

tonahoyos
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

tonahoyos
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

MuS
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

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...