topic Re: Eventstats command in Splunk Search

Eventstats command

marco_massari11 — Fri, 31 May 2024 17:19:19 GMT

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?

Re: Eventstats command

marysan — Fri, 31 May 2024 17:20:42 GMT

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

Re: Eventstats command

marcomassari1 — Fri, 31 May 2024 15:56:21 GMT

Hello @marysan ,

the query is the same

Re: Eventstats command

marysan — Fri, 31 May 2024 16:19:40 GMT

sorry

I made edits to it
Now it must works

Re: Eventstats command

marco_massari11 — Mon, 03 Jun 2024 10:57:42 GMT

Hello @marysan ,

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

Re: Eventstats command

ITWhisperer — Mon, 03 Jun 2024 11:28:57 GMT

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

Re: Eventstats command

marco_massari11 — Mon, 03 Jun 2024 12:12:43 GMT

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

Re: Eventstats command

ITWhisperer — Mon, 03 Jun 2024 12:26:51 GMT

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

Re: Eventstats command

marco_massari11 — Mon, 03 Jun 2024 13:52:21 GMT

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