Insplunktion: Finding excessive outgoing emails using splunk

One of the things that causes us no ends of issues where I work is people trying to phish usernames and passwords from our users. Every now and then one slips through the cracks and results in us having a compromised account. Normally we don’t know this until the user reports that all their email is gone and they can’t get any new email.

I decided to make splunk actively monitor for compromised accounts being used for spam. Our mail logs (postfix in this case) go into splunk so we have a vast amount of raw data that we can sort though to work out whats going on. This is the query I use to notify us of excessive outgoing email by a single person on site.

host=10.10.10.1*
    | transaction sophosqid, milterqid maxspan=5m maxpause=1m
    | search outbound NOT (t="*exmaple.com>" OR t="*another.example.org>")
        [search host=10.10.10.1*
        | transaction sophosqid, milterqid maxspan=5m maxpause=1m
        | search outbound f=*\.*@* NOT (t="*exmaple.com>" OR t="*another.example.org>")
        | stats sum(nrcpt) as Emails by f
        | where Emails>400
        | fields + f]
    | table from, to, S
    | sort From

Looks complex and confusing when you first see it. So the first things to understand is that our mail logs all contain a QID. Previous work I’ve done using props.conf extracts these QIDs from the log entries. Using these QIDs we are able to use the transaction command to combine all of the events into a single event for an email transaction.

Lets break down the query, so [ ] indicates a sub search, so we’ll look at that first:

search host=10.10.10.1*
    | transaction sophosqid, milterqid maxspan=5m maxpause=1m
    | search outbound f=*\.*@* NOT (t="*exmaple.com>" OR t="*another.example.org>")
    | stats sum(nrcpt) as Emails by f
    | where Emails>400
    | fields + f

The query is made up of the following components:

1. Return all events that are from hosts 10.10.10.1* (these are my mail servers)
2. Groups all of the events that have the same sophosqid or the same milterqid into one event so you end up with the complete conversation for the email in one event.
3. Keep any events that are from one of our users and is to an outbound address that is not one of our own.
4. Take the events that are left, group them by the from address and sum up the ncrpts field and put the result into Emails
5. Only keep any events that show we have had more than 400 recipients from this person
6. Return only the from address (so we end up of a list of people who sent 400+ emails)

So this subquery simply searches for anyone who has sent more than 400 email in the search timeframe, and returns their email address. So now that we have their email address, we want to see what emails they have sent.

host=10.10.10.1*
    | transaction sophosqid, milterqid maxspan=5m maxpause=1m
    | search outbound NOT (t="*exmaple.com>" OR t="*another.example.org>") [subsearch results above]
    | table from, to, S
    | sort From
So as you can see with the subsearch removed, the query looks nearly identical to the subsearch.

1. Return all events that are from hosts 10.10.10.1* (these are my mail servers)
2. Groups all of the events that have the same sophosqid or the same milterqid into one event so you end up with the complete conversation for the email in one event.
3. Keep any events that are outbound and not an address that is not one of our own, and only from people who have sent 400+ emails (from our subsearch).
4. Create a table that contains the From, To, and Subject of all the events
5. Sort the results so in the table, all of the email from a person appear grouped together.

So thats it, simple when you look at it 🙂

To activate this rule, I have an alert set up to run this query every 15 minutes, and email me with a URL I can follow to list out the emails that triggered the alert. I can then determine if it’s really spam, or a false positive.