topic Re: Filter events using Distinct Count instead of dedup in Splunk Search

Filter events using Distinct Count instead of dedup

beriwalnishant — Thu, 11 Feb 2021 15:22:42 GMT

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'

Regards

Nishant

Re: Filter events using Distinct Count instead of dedup

ITWhisperer — Thu, 11 Feb 2021 15:33:31 GMT

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.

Re: Filter events using Distinct Count instead of dedup

beriwalnishant — Thu, 11 Feb 2021 15:39:25 GMT

Thanks

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

Re: Filter events using Distinct Count instead of dedup

ITWhisperer — Thu, 11 Feb 2021 16:13:07 GMT

Rather dedup try

| stats count by customertripid success | stats count by success

Re: Filter events using Distinct Count instead of dedup

beriwalnishant — Fri, 12 Feb 2021 03:48:08 GMT

I am afraid but this didn't work.

Re: Filter events using Distinct Count instead of dedup

ITWhisperer — Fri, 12 Feb 2021 10:34:20 GMT

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?

Re: Filter events using Distinct Count instead of dedup

beriwalnishant — Fri, 12 Feb 2021 12:34:40 GMT

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)

I tried using your method below but no results returned

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

Re: Filter events using Distinct Count instead of dedup

ITWhisperer — Fri, 12 Feb 2021 13:13:28 GMT

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

Re: Filter events using Distinct Count instead of dedup

beriwalnishant — Sat, 13 Feb 2021 11:02:14 GMT

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