I am trying to create a dash which uses tokens for different clients capturing any attachments sent externally.
I have extracted the domain name from my search using regex so I can then exclude the internal "to" address so that all emails being recorded are external.
The exclusion is where I am getting stuck if anyone has any ideas?
index=test sourcetype="messagetracker" attachments=true
client_code=$client$
| eval timestamp=strftime(_time, "%m-%d-%Y %H:%M:%S")
| rename fromEnv.emailAddress as from_envelope_address, fromHdr.emailAddress as from_header_address, from.displayableName as from_displayname, fromHdr.displayableName as from_header_displayname, to.displayableName as to_displayname, to{}.displayableName as to_array_displayname, from.emailAddress as from_address, fromHeader.emailAddress as from_header2_address, to.emailAddress as to_address, to{}.emailAddress as to_array_address
| search from_envelope_address=*
| rename to_array_address as to
| rename from_envelope_address as from
|search to="***" from="***"
| rex field=from "\@(?<domain>[^ ]*)"
|search NOT (to in domain)
| dedup from,to, subject, status, info, route
| fillnull value="n/a" from_header_address
| table timestamp, from, to,domain, attachments, status, info, route, spamScore, client_code
Instead of looking for to in domain, look for domain in to. One way to do this is to convert multivalue to into a plain string:
| eval to = mvjoin(to, ",")
| where NOT match(to, "^[^,]+@".domain."$")
Given composite dataset
domain | from | to |
abc.com | body@abc.com | body@abc.com body@bcd.com body@cde.com |
abc.com | body@abc.com | body@abc.com |
abc.com | body@abc.com | body@def.com body@efg.com |
abc.com | body@abc.com | body@fghcom body@abc.com |
output is
domain | from | to |
abc.com | body@abc.com | body@abc.com,body@bcd.com,body@cde.com |
abc.com | body@abc.com | body@def.com,body@efg.com |
abc.com | body@abc.com | body@fgh.com,body@abc.com |