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

[DerekKing]

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

[prelert]

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/

[DerekKing]

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.

[lguinn2 · Accepted solution]

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...

[lguinn2]

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

[DerekKing]

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 ?

[lukejadamec]

Can you post some event examples?

Is the email subject extracted as a field already?