Getting Data In

How to filter events if a field value must be false and then true at a later point?

phil81
Explorer

Hi Splunk community,

I was not sure how to formulate the question precisely, so I give you my use case:

  • Filter for failed user transactions to determine lost revenue and exclude recovered transactions, i.e. from those users, who were able to make a successful transaction at a later point

This means that a user can make multiple failed transactions/attempts but if at least one of them was successful, all of the user´s transactions/events should be removed from the results.

Example fields and values:
userid=ABC
transactionsuccess=true/false
value=100

I can get the number and total value of all failed transactions using
index=myindex transactionsuccess=false | stats sum(value) AS revenueloss | dedup userid
and all the successful ones with
index=myindex transactionsuccess=true | stats sum(value) AS revenue| dedup userid
Now I just need to find out how to join those two queries into one. I could export both results to excel and filter for all failed events by userid which have no corresponding successful event for the same userid to achieve my goal, but I guess there is an easier way to do this directly in Splunk.

Thanks,
Phil

0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this

index=myindex transactionsuccess=false OR transactionsuccess=true
| chart sum(value) over userid by  transactionsuccess

Now above query should give sum of revenue, for each userid, with column names false (sum of revenue for all failed transactions by user) and true(sum of revenue for all successful transactions by user). So, you could simply filter the records using following where clause to only keep users which have only failed transactions, which means value in true column is 0.

above query | where true=0 | table userid false | rename false as "LostRevenue"

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Perhaps this will help

index=myindex | eval loss=if(transactionsuccess=false, value, 0) | stats sum(loss) as revenueloss latest(transactionsuccess) as success by userid | where success == false
---
If this reply helps you, Karma would be appreciated.

somesoni2
Revered Legend

Try something like this

index=myindex transactionsuccess=false OR transactionsuccess=true
| chart sum(value) over userid by  transactionsuccess

Now above query should give sum of revenue, for each userid, with column names false (sum of revenue for all failed transactions by user) and true(sum of revenue for all successful transactions by user). So, you could simply filter the records using following where clause to only keep users which have only failed transactions, which means value in true column is 0.

above query | where true=0 | table userid false | rename false as "LostRevenue"

phil81
Explorer

Thanks, the first part worked fine, I got it the second part working when changing it to

| where isnull(true)
This returns the userids which have only failed transaction(s), together with the value. There is still a part missing: Because of the sum(value) I get an overstated number, e.g. if a user has 3 failed attempts, in our query the loss value is 3 times the transaction value (sum of each individual lost transaction), when in fact, the user would have generated only the value/revenue of one transaction, if successful.

Is there a way to divide the sum(value) by failed transaction counts, i.e. occurrences of transactionsuccess=false per userid?

0 Karma

somesoni2
Revered Legend

Try using avg or max or min function instead of sum (assuming each failed event has same value).

0 Karma

phil81
Explorer

Perfect, I appended an |chart sum(false) and have now exactly what I needed.

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