topic Re: Counting Emails with the same subject, and reporting higher than average in Splunk Search

Counting Emails with the same subject, and reporting higher than average

DerekKing — Tue, 25 Feb 2014 17:54:44 GMT

Hi All,

I've had an incident where phishing email has come through my reputation filter, and it got me to thinking splunk must be able to look for emails with the same subject, take the average on a per_hour basis and hold a running total of some description. Then each hour if the number of emails with the same subject exceeds the first value it should alert.

So - If 'normally' its possible to see 2 emails per hour with the same subject, then at some point I see 5 emails, this could alert me to a phishing attack.

I've looked a anomalies, and I think it may be able to help, but i'm not sure at this minute how to even count entries with the same subject.

Any help on this is appreciated.
Derek

Re: Counting Emails with the same subject, and reporting higher than average

lukejadamec — Tue, 25 Feb 2014 18:28:37 GMT

Can you post some event examples?

Is the email subject extracted as a field already?

Re: Counting Emails with the same subject, and reporting higher than average

lguinn2 — Tue, 25 Feb 2014 19:02:08 GMT

I would probably do something like this

sourcetype=email OR whatever_you_need earliest=-7d
| timeframe=if(_time > now()-3600,"LastHour","LastWeek")
| bucket _time span=1h
| stats count by subject timeframe _time
| chart avg(count) by subject timeframe
| eval PossibleProblem=if(LastHour>LastWeek,"YES","")
| rename LastHour as "Avg Emails/Hour Last Hour" LastWeek as "Avg Emails/Hour Last Week"

You might want to find a way to eliminate intra-company conversations, though...

Re: Counting Emails with the same subject, and reporting higher than average

DerekKing — Tue, 25 Feb 2014 19:21:55 GMT

Hi Yes, the subject field is already extracted, so I can do something like | stats count AS BigSubjects BY Subject which has me in the right direction....
Its difficult to post data as there is too much to anonomize. I'm working with cisco_esa though.

Re: Counting Emails with the same subject, and reporting higher than average

DerekKing — Tue, 25 Feb 2014 19:30:38 GMT

thanks for this, i've not got my head around how its working at the minute, but splunk tells me there is an error in the timechart, timeframe is not a valid argument. Looking at the docs, I can't supply more than one argument to the BY clause ?

Re: Counting Emails with the same subject, and reporting higher than average

lguinn2 — Tue, 04 Mar 2014 07:15:28 GMT

Gack - sorry about that - I've revised my answer above...

Re: Counting Emails with the same subject, and reporting higher than average

prelert — Tue, 02 Sep 2014 15:56:33 GMT

A generic approach could be to run:

sourcetype=email | prelertautodetect count by subject

This automatically baselines the periodic variations in the data.

https://apps.splunk.com/app/1306/