Splunk Search

Calculating a sum with conditions

Jurala
Explorer

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)

0 Karma
1 Solution

cmerriman
Super Champion

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

View solution in original post

cmerriman
Super Champion

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
Explorer

That's it, thanks a lot!

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...