Splunk Search

Eventstats command

marco_massari11
Communicator

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?

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

marysan
Communicator

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
0 Karma

marcomassari1
New Member

Hello @marysan ,

the query is the same

0 Karma

marysan
Communicator

sorry

I made edits to it 
Now it must works 

marco_massari11
Communicator

Hello @marysan ,

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

marco_massari11_0-1717412251427.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

marco_massari11
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...