Securing email relay: Is there a way to lookup a table and list out non matching "from email addresses"?

[tonyxavierj]

We use exchange 2013 and relay permission is given to certain machines(IP's). These machines can send email as any existing or non existent user under our domain. but they are only allowed to send email from a particular email address.

So far I have achieved the following

created an alert if a machine sends an email from another email address which is not allowed or approved. but this works only for a search like

index="myindex" OriginalClientIp="10.x.x.x" NOT Sender="non-existent_user@domain.com" | table Sender Recipients Timestamp OriginalClientIp

I have a list of email addresses and IP's. 

There will be a max of two email addresses from each IP

any way to lookup a table and list out non matching "from email addresses"?

[maciep]

This seems doable.  Let's assume you create a lookup table called senders with fields "ip" and "email", where you may have multiple rows for the same IP Address since you could have multiple emails for each ip.

With that assumption, I think you could handle it two ways.  One, you could get all of the events and then filter for the ones you want.  Second, you could use a subsearch to help build a search that only got you the events you were after in the first place.

Examples, not really tested:

The first way.  Find all of the events, get the email addresses for the ip in the event, and include events where the Sender isn't in that list

index="myindex"
| lookup senders ip AS OriginalClientIp OUTPUT email
| where isnull(mvfind(email,Sender))

The second way, build the search condition from the lookup first, then run the search.  This should resolve to something like:

index=myindex (OriginalClientIp=x.x.x.x AND NOT (Sender="user1@isp.com" Sender="user2@isp.com)) OR (OriginalClientIp=x.x.x.y AND NOT (Sender="user3@isp.com" Sender="user4@isp.com)) ....

Not sure if all of the parens/quotes are right, but the basic idea is here

index=myindex
[
| inputlookup senders
| eval email = "Sender=\"" . email . "\""
| stats values(email) as emails by ip
| eval emails = mvjoin(emails," OR ")
| eval conditions = "(OriginalClientIp=" . ip . " AND NOT (" . emails . "))"
| stats values(conditions) as conditions
| eval conditions = mvjoin(conditions, " OR ")
| return $conditions
]