topic Re: Stats Distint Count in Splunk Search

Stats Distint Count

IRHM73 — Wed, 16 Mar 2016 14:20:35 GMT

Hi, I wonder whether someone may be able to help me please.

I'm using the query below to extract a piece of data.

index=main tags.transactionName=print-suppression
    | eval Date=substr(generatedAt, 1, 10) | stats dc(Date) by Date
    | eval verifiedButBounced=coalesce('detail.verifiedButBounced.count',0)
    | eval pendingVerificationOfChangedEmail=coalesce('detail.pendingVerificationOfChangedEmail.count', 0)
    | eval verifiedPendingVerificationOfChangedEmailAndBounced=coalesce('detail.verifiedPendingVerificationOfChangedEmailAndBounced.count', 0)
    | eval verifiedButBouncedPendingVerificationOfChangedEmail=coalesce('detail.verifiedButBouncedPendingVerificationOfChangedEmail.count', 0)
    | eval verifiedButBouncedPendingVerificationOfChangedEmailAndBounced=coalesce('detail.verifiedButBouncedPendingVerificationOfChangedEmailAndBounced.count', 0)
    | eval verifiedNotBounced=coalesce('detail.verifiedNotBounced.count', 0)
    | eval Verified=verifiedButBounced + pendingVerificationOfChangedEmail +  verifiedPendingVerificationOfChangedEmailAndBounced + verifiedButBouncedPendingVerificationOfChangedEmail + verifiedButBouncedPendingVerificationOfChangedEmailAndBounced + verifiedNotBounced
    | rename detail.totalOfAllOptedIn.count AS Opted_In, detail.optedOut.count AS Opted_Out 
    | table Date, Opted_In, Opted_Out, Verified, detail.verifiedButBounced.count, detail.verifiedNotBounced.count, detail.pendingVerificationOfChangedEmail

The problem I have is that I want eliminate duplicate dates. Now I know that I can use 'dedup' which successfully removes the duplicates but I've been reading that stats dc or 'distinct count' is more efficient, but I'm a little unsure about how to incorporate this.

I just wondered whether someone could possibly look at this please and offer some guidance on where I should incorporate the 'stats dc' command.

Many thanks and kind regards

Chris

Re: Stats Distint Count

alacercogitatus — Wed, 16 Mar 2016 15:14:53 GMT

You could do it at the tail end of that search.

<that_Search> | stats last(*) as * by Date

This will take the "last" of each value on each date, and output it. You can also use "first" to get the first event found.

Remove the |stats dc(Date) by Date, since that is probably interfering with the search.

Re: Stats Distint Count

IRHM73 — Wed, 16 Mar 2016 15:24:46 GMT

Hi all,

Just to let you know that I've continued to work on this and created a solution here:

index=main tags.transactionName=print-suppression
| eval Date=substr(generatedAt, 1, 10)
| eval verifiedButBounced=coalesce('detail.verifiedButBounced.count',0)
| eval pendingVerificationOfChangedEmail=coalesce('detail.pendingVerificationOfChangedEmail.count', 0)
| eval verifiedPendingVerificationOfChangedEmailAndBounced=coalesce('detail.verifiedPendingVerificationOfChangedEmailAndBounced.count', 0)
| eval verifiedButBouncedPendingVerificationOfChangedEmail=coalesce('detail.verifiedButBouncedPendingVerificationOfChangedEmail.count', 0)
| eval verifiedButBouncedPendingVerificationOfChangedEmailAndBounced=coalesce('detail.verifiedButBouncedPendingVerificationOfChangedEmailAndBounced.count', 0)
| eval verifiedNotBounced=coalesce('detail.verifiedNotBounced.count', 0)
| eval Verified=verifiedButBounced + pendingVerificationOfChangedEmail +  verifiedPendingVerificationOfChangedEmailAndBounced + verifiedButBouncedPendingVerificationOfChangedEmail + verifiedButBouncedPendingVerificationOfChangedEmailAndBounced + verifiedNotBounced
| stats dc(Date) by Date, Verified, detail.totalOfAllOptedIn.count, detail.optedOut.count, detail.verifiedButBounced.count, detail.verifiedNotBounced.count, detail.pendingVerificationOfChangedEmail.count
| rename detail.totalOfAllOptedIn.count AS Opted_In, detail.optedOut.count AS Opted_Out 
| table Date, Opted_In, Opted_Out, Verified, detail.verifiedButBounced.count, detail.verifiedNotBounced.count, detail.pendingVerificationOfChangedEmail.count

Many thanks and kind regards

Chris

Re: Stats Distint Count

IRHM73 — Wed, 16 Mar 2016 15:26:15 GMT

Hi, thank you for taking the time to reply to my post.

I did try the solution you kindly provided, but unfortunately it didn't return any results, but I have managed to put together a solution.

Many thanks and kind regards

Chris