Splunk Search

distinct count using stats and eval

Communicator

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

0 Karma
1 Solution

Motivator

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)."%"

View solution in original post

Motivator

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)."%"
0 Karma

Motivator

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)."%"

View solution in original post

Motivator

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)."%"

OR

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)."%"
0 Karma

Communicator

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

Cheers,
Alastair

Communicator

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)

0 Karma