Splunk Search

splunk stats group and count by fields

Ameenulla
Engager

need query to remove duplicates from count stats

Sample input

event  email

abc      xyz@email.com

abc    xyz@email.com

abc. test@email.com

abc. test@email.com

xyz xyz@email.com

Expected output 

eventcount
abc2
xyz1

what I am getting 

eventcount
abc4
xyz1
Labels (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

It is good that you try to illustrate input and desired output.  But you forget to tell us what you are trying to count that should either be 4 or 2?  In other words, you need to explain the logic between input and desired output fully and explicitly.

If I take a wild mind reading, you want to count unique number of E-mails related to each type of event.  You want to use distinctcount or dc, not count.

 

| stats dc(email) as count by event

 

Here's an emulation of your mock input

 

| makeresults format=csv data="_raw
abc      xyz@email.com
abc    xyz@email.com
abc. test@email.com
abc. test@email.com
xyz xyz@email.com"
| rex "(?<event>\w+)\W+(?<email>\S+)"
``` data emulation above ```

 

The output is

eventcount
abc2
xyz1

View solution in original post

0 Karma

yuanliu
SplunkTrust
SplunkTrust

It is good that you try to illustrate input and desired output.  But you forget to tell us what you are trying to count that should either be 4 or 2?  In other words, you need to explain the logic between input and desired output fully and explicitly.

If I take a wild mind reading, you want to count unique number of E-mails related to each type of event.  You want to use distinctcount or dc, not count.

 

| stats dc(email) as count by event

 

Here's an emulation of your mock input

 

| makeresults format=csv data="_raw
abc      xyz@email.com
abc    xyz@email.com
abc. test@email.com
abc. test@email.com
xyz xyz@email.com"
| rex "(?<event>\w+)\W+(?<email>\S+)"
``` data emulation above ```

 

The output is

eventcount
abc2
xyz1
0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Take Action Automatically on Splunk Alerts with Red Hat Ansible Automation Platform

 Are you ready to revolutionize your IT operations? As digital transformation accelerates, the demand for ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...

Beyond Detection: How Splunk and Cisco Integrated Security Platforms Transform ...

Financial services organizations face an impossible equation: maintain 99.9% uptime for mission-critical ...