Calculating a sum with conditions

[Jurala]

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 my query:
index=aa sourcetype=bb
| dedup ID
| eval subtotal=0
| foreach summa [eval subtotal = subtotal + '<>']
| chart sum(subtotal) by userID
| where applicationStatus=c OR applicationStatus=n
| sort sum(subtotal)

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

index=aa sourcetype=bb applicationStatus=n OR c
| dedup ID
| eval subtotal=0
| foreach summa [eval subtotal = subtotal + '<>']
| chart sum(subtotal) by userID
| sort sum(subtotal)

[cmerriman · Accepted solution]

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

[Jurala]

That's it, thanks a lot!