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

[phil81]

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

[richgalloway]

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

[somesoni2 · Accepted solution]

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]

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?

[somesoni2]

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

[phil81]

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