topic Re: How to count multiple events per line in Splunk Search

How to count multiple events per line

Dallastek — Wed, 28 Jan 2015 20:01:49 GMT

Im trying to count how many events by category per email domain and do a total of events going to each domain. My query looks like this (note I macroed all the different email domains)

sourcetype=mysearch `myemail`
| stats  values(Dst_Email_Domain) AS Email_Domain count by Name  
| sort  - count 
| stats list values(Name) sum dc(Name) by Email_Domain 
| sort  - sum(count) 
| rename  "list(Name)" TO Category, list(count) TO Count, sum(count) TO Total 
| fields Email_Domain, Category, Count, Total

So this gives me 4 columns: Email_Domain, Category, Count, Total. I get a count in Category for each domain but the count is the same for each item in category e.g. invoice=50, customer bill=100, credit card=75 across all email domains. the Totals seems right, but I can't get the individual events in the category field to count right per each domain. (hope that explains it)

Re: How to count multiple events per line

bmacias84 — Wed, 28 Jan 2015 20:16:15 GMT

Can you post an sample of scrubbed events? what might help

Re: How to count multiple events per line

Dallastek — Wed, 28 Jan 2015 20:29:53 GMT

output looks something like this: (note im only 1 mo. into using splunk) the counts in the category column are the same for all domains, the total column seems to have the correct counts for the # of emails going out, but it does not match up with the category column.
Email_Domain Category Count Total
gmail.com customerbill 100 470
creditcard 50
Invoice 100
confidential 220
yahoo.com customerbill 100 250
creditcard 50
Invoice 100
hotmail.com customerbill 100 300
creditcard 50
Invoice 100
confidential 220

Re: How to count multiple events per line

lguinn2 — Wed, 28 Jan 2015 20:31:10 GMT

The problem happens in step 3 - you have grouped all of your email domains into a single multi-valued variable. After that, in step 5, you can no longer count by each value of email domain. Try this:

 sourcetype=mysearch `myemail`
 | stats count as Count by Name  Email_Domain
 | appendpipe [ stats sum(Count) as Total by Email_Domain | eval Name="**Total**" ]
 | sort Email_Domain

This may not order exactly as you want, so you could also do this

 sourcetype=mysearch `myemail`
 | stats count as Count by Name Email_Domain
 | eventstats sum(Count) as sortTotal
 | appendpipe [ stats sum(Count) as Total by Email_Domain | eval Name="**Total**" ]
 | sort -sortTotal Email_Domain Name
 | fields -sortTotal

I am not sure if the email domain total will sort to the end, but you can try it.

Re: How to count multiple events per line

Dallastek — Wed, 28 Jan 2015 20:51:56 GMT

Didn't work, most of the fields in my table end up being blank
that either gives me no results or just a value of 1 100 times

Re: How to count multiple events per line

lguinn2 — Wed, 28 Jan 2015 22:43:30 GMT

Okay, what do you get if you do this?

  sourcetype=mysearch `myemail`
  | stats count as Count by Name Email_Domain

Re: How to count multiple events per line

Dallastek — Wed, 28 Jan 2015 23:08:07 GMT

all I get is a count of every instance of email domain (count of 1) and all other fields are blank, maybe I am substituting in the wrong place, I replaced step 3 with your latest

Re: How to count multiple events per line

lguinn2 — Thu, 29 Jan 2015 06:42:38 GMT

Just run the following search all by itself: you should get 3 columns: Name Email_Domain Count

They should make sense to you.

I just want to see if you can get the basic counts or if there is something more fundamentally wrong here.

   sourcetype=mysearch `myemail`
   | stats count as Count by Name Email_Domain

Re: How to count multiple events per line

Dallastek — Mon, 28 Sep 2020 18:45:16 GMT

That search did give me the 3 columns,
After playing with it some more and using some of your suggestions as a guideline this is what we came up with and it works perfectly
sourcetype=mysearch myemail | top Name by Dst_Email_Domain limit=500 | stats list(Name), list(count), sum(count) by Dst_Email_Domain

guess I was over thinking it earlier