Splunk Search

Filter events using Distinct Count instead of dedup

beriwalnishant
Path Finder

Hello All,

 

May I request you to help me with the query below 

 

I have two fields "customertripid & success"

Customertripid has a unique id for a transaction - the transaction offers re-attempts on the same customertripid - so one transaction equal to one customertripid

Problem :
Success=False
I want to capture all the events with unique customertripid where success=false (include those which passed eventually in reattempts) - I want to count them and use it to do %

Success=Pass
That is giving correct counts...basically picking up the last attempt on each customertripid either 'passed' or 'failed'

See the count here of fails when 'success=false' and when 'success=true'

beriwalnishant_4-1613056815101.png

Regards

Nishant

 
 

 

 



 

 

Labels (1)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

The reason you get no results is that the stats command returns two fields customertripid and success so the chart command does not have a field tpid to work with. Either customertripid should be tpid or vice versa depending on the fields returned by the search.

Having said that, I am not sure why you are not doing

| stats count(eval(success="false")) as Total_Failed count(eval(success="true")) as Total_Passed count as Total by customertripid
| eval Failed_Ratio=(round((Total_Failed/Total)*100,2)
| where Failed_Ratio > 0
| fields customertripid, Failed_Ratio, Total

 Main issue with this is if a passed transaction is reattempted and passes or fails again as this skews the results (slightly). Also, do you need to know if any the transaction passed? If so, you could keep the Total_Passed field as well

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

When success=false, the dedup will pick up all the customertripid which have failed

When success=*, the dedup will pick up one of the status for the customertripid so the failed count will be lower when there has been a success.

The results you have shown bear this out.

beriwalnishant
Path Finder

Thanks

Sorry, does that mean there is no way out to this 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Rather dedup try

| stats count by customertripid success
| stats count by success
0 Karma

beriwalnishant
Path Finder

My bad on not adding more details - I mean by standalone it works however I want to do this...

how can I use this to do this .... using your solution (this dedup doesnt count a txn that followed series of fail attempts before getting passed bearing same customertripid (see explanation below)

beriwalnishant_0-1613132981952.png

 

I tried using your method below but no results returned

 

beriwalnishant_1-1613132998258.png

 

beriwalnishant_2-1613133001956.png

 

 

That's where the struggle is - if I can do this I basically would be able to count each transaction on its last status....each transaction bears a customertripid.....and each transaction gets reattempted....all the following re-attempts bears the same customertripid 

The end result could be that the transaction after a number of attempts (3 or 4 or 5 depending upon the front user) stops at as 'fail' or stops at 'pass'

Irrespective of it being passed I want to pick the last 'fail' to get the count of those fails also which passed so that we know that if a transaction was 'passed' did it follow any re-attempts due to being failed or just a pass

 

Thanks again and sorry for saying your solution didnt work....I mean I didnt add all the details to tell you how your solution to make work the way I want


Nishant

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The reason you get no results is that the stats command returns two fields customertripid and success so the chart command does not have a field tpid to work with. Either customertripid should be tpid or vice versa depending on the fields returned by the search.

Having said that, I am not sure why you are not doing

| stats count(eval(success="false")) as Total_Failed count(eval(success="true")) as Total_Passed count as Total by customertripid
| eval Failed_Ratio=(round((Total_Failed/Total)*100,2)
| where Failed_Ratio > 0
| fields customertripid, Failed_Ratio, Total

 Main issue with this is if a passed transaction is reattempted and passes or fails again as this skews the results (slightly). Also, do you need to know if any the transaction passed? If so, you could keep the Total_Passed field as well

0 Karma

beriwalnishant
Path Finder

Ohh you have no idea that you really solved it the way I wanted. I continued to add the fields in first stats as

| stats count by customertripid, success, tpid, morefield, morefield

 

Than keep your stats eval count separate like normal. What all field you want continue to add it. 
you have given such a simple solution to a complex problem that I couldn’t find anywhere, even in my office’s splunk support team. 

hats off to you. thanks a lot to you. 

Regards

Nishant

Tags (1)
0 Karma

beriwalnishant
Path Finder

I am afraid but this didn't work.



0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

In what way does this not work?

The first stats gives you a count of event for each combination of customertripid and success

The second stats gives you the number of unique customertripid for each type of success - is this not what you wanted?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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