Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How can I run stats sum as command on same search ...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

tonahoyos

Explorer

10-09-2017
02:19 PM

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?

1 Solution

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

MuS

SplunkTrust

10-09-2017
02:36 PM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

tonahoyos

Explorer

10-11-2017
11:37 AM

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,

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

tonahoyos

Explorer

10-13-2017
10:16 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

MuS

SplunkTrust

10-13-2017
10:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

justinatpnnl

Communicator

10-09-2017
04:38 PM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

tonahoyos

Explorer

10-11-2017
11:29 AM

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?

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

justinatpnnl

Communicator

10-11-2017
11:37 AM

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

tonahoyos

Explorer

10-13-2017
10:16 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

justinatpnnl

Communicator

10-13-2017
01:42 PM

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)`