How to count multiple events per line

[Dallastek]

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)

[lguinn2 · Accepted solution]

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.

[Dallastek]

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

[lguinn2]

Okay, what do you get if you do this?

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

[Dallastek]

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

[lguinn2]

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

[Dallastek]

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

[bmacias84]

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

[Dallastek]

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