topic Calculating a sum with conditions in Splunk Search

Calculating a sum with conditions

Jurala — Thu, 05 Oct 2017 07:10:48 GMT

Hi all!

The case is that I want to calculate sum of purchase price of the applications where the application status is either c(contacted) or n(new). There's also multiple other application statuses. Each Splunk event has a unique ID for each event so I will first dedup the ID out so the latest application status with unique ID will be present only. The issue is that I want to calculate the sum, where the application status is either c or n. If application status search is used in query it won't include won(w) and lost(l) application to the search thus calculating sum of applications which status has already been changed to other status than c or n. I've tried to use where command but I don't get any results with that.

Here's another query:
Issue with this one is that if the application status has been changed to w(won) it wont effect to result

Re: Calculating a sum with conditions

cmerriman — Thu, 05 Oct 2017 12:43:06 GMT

i think your problem with the first query is that chart isn't giving you a field called applicationStatus. it should only be giving you fields like sum(subtotal): userId1

could you do something like:

    index=aa sourcetype=bb
    | dedup ID
    |eventstats sum(summa) as grandTotal by userId
    |stats sum(summa) as subTotal max(grandTotal) as grandTotal by userId applicationStatus
    |search applicationStatus="c" OR applicationStatus="n"
    |stats sum(subTotal) as subTotal max(grandTotal) as grandTotal by userId

Re: Calculating a sum with conditions

Jurala — Fri, 06 Oct 2017 07:44:15 GMT

That's it, thanks a lot!