Splunk Search

How can I subtract two results from stats?

guimilare
Communicator

Hi all.

I'm having a hard time trying to make a subtraction..

This is my entry csv:

Date,category,amount,person
01/08/2015,debit,150.00,jose
01/08/2015,debit,130.00,mary
07/08/2015,credit,300.00,jose

What I have so far is:

index=<my_index> | stats sum(amount) as Result by category | addcoltotals labelfield=category label=Total

category                                              Result
debit                                                 280.00
credit                                                300.00
Total                                                 580.00

However, what I want is the difference between Credit and Debit, something like this:

category                                              Result
debit                                                 280.00
credit                                                300.00
Total                                                 20.00

Any ideas how I should write my search?

Thank in advance.

Tags (3)
0 Karma
1 Solution

kdoonan
Explorer

Hi Guimilare,

You could try multiplying one part by -1

index=someindex | eval amount=IF(category=="debit", -1 * amount, amount) | stats sum(amount) as Result by category | addcoltotals labelfield=category label=Total

View solution in original post

0 Karma

kdoonan
Explorer

Hi Guimilare,

You could try multiplying one part by -1

index=someindex | eval amount=IF(category=="debit", -1 * amount, amount) | stats sum(amount) as Result by category | addcoltotals labelfield=category label=Total

0 Karma

guimilare
Communicator

I don't get any errors.. But I get the same result as before...

0 Karma

guimilare
Communicator

Hi kdoonan, it worked now!
The thing was that -1 shold come after the field amount:

index=someindex | eval amount=IF(category=="debit", amount*-1, amount) | stats sum(amount) as Result by category | addcoltotals labelfield=category label=Total

Thank you!!

0 Karma

guimilare
Communicator

Hi kdoonan,

It didn't work.
The IF statment is not working... I've tried to change to a positive number (e.g., 10), and no luck either..

Thank you

0 Karma

kdoonan
Explorer

What error do you get when you try to run it and do you have it in the same part of the search?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...