topic distinct count using stats and eval in Splunk Search

distinct count using stats and eval

ahogbin — Wed, 23 Nov 2016 03:34:26 GMT

I am slowly going insane trying to figure out how to remove duplicates from an eval statement.

where acc="Inc" AND Stage = "NewBusiness"  | stats dc(quoteNumber) AS Quotes count(eval(processStatus="ManualRatingRequired")) as Referrals |eval perc=round(Referrals/Quotes*100, 1)."%"

The problem I am having is that whilst I have been able to remove the duplicates for the first stats (Quotes) I am unable to remove them for the eval component. If I use distinct count then only 1 even is returned and if i use distinct count with a filter by quoteNumber then all works and the duplicates are removed... however the results are returned as separate events in table format.

I am after distinct count of all quotes / a distinct count of all quotes that have a processStatus of Referred.

Sounds easy (and it probably is) but it is doing my head in and I am not getting any closer to a solution.... so for the sake of my sanity any help will be great appreciated.

Cheers,

Alastair

Re: distinct count using stats and eval

gokadroid — Wed, 23 Nov 2016 04:41:30 GMT

Why don't you insert a dedup just before you start going for your stats like this and see if that works for you:

where acc="Inc" AND Stage = "NewBusiness"  
| dedup quoteNumber  processStatus
| stats dc(quoteNumber) AS Quotes count(eval(processStatus="ManualRatingRequired")) as Referrals |eval perc=round(Referrals/Quotes*100, 1)."%"

Updating as per comments

where acc="Inc" AND Stage = "NewBusiness"  
 | eventstats dc(quoteNumber) AS Quotes
 | dedup processStatus, quoteNumber
 |  stats values(Quotes) as UniqueQuotes count(eval(processStatus="ManualRatingRequired")) as Referrals 
 |eval perc=round(Referrals/UniqueQuotes*100, 1)."%"

Re: distinct count using stats and eval

ahogbin — Wed, 23 Nov 2016 05:15:08 GMT

Tried that.. the problem with the dedup is that it only returns the latest event and removes some of the events I am trying to included.

Eg: If I do count of quotes for the current day I get 36. If I remove he duplicates I get 25.
If I do the same for those quotes that have triggered a referral I end up with 17 (including duplicates) and 12 if I remove the duplicates.

Using dedup and a count by processStatus gives me only 10 referrals.

For any quote there can be multiple entries

QN1- Referred
QN1 - Completed
QN1 - Closed
QN2 - Completed
QN2 - Closed

Dedup returns QN1 - Closed (as this is the latest event).

Is there a way to return a dc(Quotes) and a dc(Quotes) where status = Referred (eg using the above as a guide - 2 quotes and 1 referral)

Re: distinct count using stats and eval

gokadroid — Wed, 23 Nov 2016 07:18:47 GMT

I still think that this query should give you what's needed as it does a dedu on the processStatus and quoteNumber so there shouldn't be a reason for it to return only the last closed event, but I have updated additional query which basically should do the same thing differently:

where acc="Inc" AND Stage = "NewBusiness"  
 | dedup quoteNumber,  processStatus
 | stats dc(quoteNumber) AS Quotes count(eval(processStatus="ManualRatingRequired")) as Referrals |eval perc=round(Referrals/Quotes*100, 1)."%"

where acc="Inc" AND Stage = "NewBusiness"  
  | eventstats dc(quoteNumber) AS Quotes
  | dedup processStatus, quoteNumber
  |  stats values(Quotes) as UniqueQuotes count(eval(processStatus="ManualRatingRequired")) as Referrals 
|eval perc=round(Referrals/UniqueQuotes*100, 1)."%"

Re: distinct count using stats and eval

chimell — Wed, 23 Nov 2016 12:45:57 GMT

Hi
Try the following search code using dedup command :

 where acc="Inc" AND Stage = "NewBusiness" |dedup quoteNumber | stats count(quoteNumber) AS Quotes count(eval(processStatus="ManualRatingRequired")) as Referrals |eval perc=round(Referrals/Quotes*100, 1)."%"

Re: distinct count using stats and eval

ahogbin — Fri, 25 Nov 2016 04:03:03 GMT

Both work a treat.. thank you so much for your help.

Cheers,
Alastair