Eventstats command

[marco_massari11]

Hello,

I'm trying to write a Splunk search for detecting unusual behavior in emails sending, here is the spl query:

| tstats summariesonly=true fillnull_value="N/D" dc(All_Email.internal_message_id) as total_emails from datamodel=Email where
(All_Email.action="quarantined" OR All_Email.action="delivered") AND
NOT
[| `email_whitelist_generic`]
by All_Email.src_user, All_Email.subject, All_Email.action
| `drop_dm_object_name("All_Email")`
| eventstats sum(eval(if(action="quarantined", count, 0))) as quarantined_count_peruser, sum(eval(if(action="delivered", count, 0))) as delivered_count_peruser by src_user, subject
| where total_emails>50 AND quarantined_count_peruser>10 AND delivered_count_peruser>0

I want to count the number of quarantined emails and the delivered ones only and than filter them for some threshold, but it seems that the eventstats command is not working as expected. I already used this logic for authentication searches and it's working fine.

Any help?

[marysan]

I think this query will work for you : 

| tstats summariesonly=true fillnull_value="N/D" dc(All_Email.internal_message_id) as total_emails from datamodel=Email where (All_Email.action="quarantined" OR All_Email.action="delivered") AND NOT [| `email_whitelist_generic`] by All_Email.src_user, All_Email.subject, All_Email.action | `drop_dm_object_name("All_Email")` | eventstats sum(eval(if(action="quarantined", 1, 0))) as quarantined_count_peruser, sum(eval(if(action="delivered", 1, 0))) as delivered_count_peruser by src_user, subject | where total_emails > 50 AND quarantined_count_peruser > 10 AND delivered_count_peruser > 0

[marcomassari1]

Hello @marysan ,

the query is the same

[marysan]

sorry

I made edits to it 
Now it must works 

[marco_massari11]

Hello @marysan ,

it's seems that the result is not as expected:

[ITWhisperer]

In what way is it not what you expected? Please share what you had expected?

[marco_massari11]

Hello @ITWhisperer ,

the result should be the total emails count, and the specific count for the delivered and quarantined ones. In my screenshot, there are for example 6 total emails (first row), and 12 delivered, which is not possible. So the a possible expectation should be:

Case1: 6 total emails, 6 delivered, 0 quarantined

Case2: 6 total emails, 3 delivered, 3 quarantined

Case3: 6 total emails, 1 delivered, 5 quarantined

[ITWhisperer · Accepted solution]

Well obviously it is possible! The "issue" is that the total emails are counted by user, subject and action, whereas the other two counts are by just user and subject. You could change the eventstats to correct this

| eventstats sum(eval(if(action="quarantined", 1, 0))) as quarantined_count_peruser, sum(eval(if(action="delivered", 1, 0))) as delivered_count_peruser sum(total_emails) as total_emails by src_user, subject

[marco_massari11]

Hello, maybe I'm missing some points but it seems that the result is the same