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