I'm needing to use multiple AND's and OR's in my where clause and the way I'm writing it is giving me inconsistent results.
The data I'm searching is sendmail logs and I'm getting the "from" and "to" fields for emails. The results I need are where the emails are sent from the domains @example1.com or @example2.com and the recipient is NOT in the domain @foobar1.com or @foobar2.com.
How I though it would go is like this, where the first 2 are grouped together then the second 2 are grouped together... like sql:
sourcetype=mail_log | transaction qid | where (like(from, "%@example1.com") OR like(from, "%@example2.com")) AND (like(to, "%@foobar1.com") OR like(to, "%@foobar2.com"))
This returns a "search job has failed" error.
I also tried to just pipe it to another where clause like this:
sourcetype=mail_log | transaction qid | where like(from, "%@example1.com") OR like(from, "%@example2.com") | where like(to, "%@foobar1.com") OR like(to, "%@foobar2.com")
But this gave me inconsistent results.
What is the proper way to write this out?
I thought the recipient didn’t contain foobar1 or foobar2? Your searches are showing where they are containing those domains.
Try something like
sourcetype=mail_log | transaction qid | where (like(from, "%@example1.com") OR like(from, "%@example2.com")) NOT like(to, "%@foobar1.com") NOT like(to, "%@foobar2.com")
Or
sourcetype=mail_log | transaction qid |eval delete=case(like(to, "%@foobar1.com") OR like(to, "%@foobar2.com"),0,like(from, "%@example1.com") OR like(from, "%@example2.com"),1)|search keep=1
You eval to the delete field and then filter for the keep field, that won't work I guess 🙂