Splunk Search

Grouping in the where clause

cliffennis
New Member

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?

0 Karma

cmerriman
Super Champion

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
0 Karma

FrankVl
Ultra Champion

You eval to the delete field and then filter for the keep field, that won't work I guess 🙂

0 Karma
Get Updates on the Splunk Community!

Detecting Brute Force Account Takeover Fraud with Splunk

This article is the second in a three-part series exploring advanced fraud detection techniques using Splunk. ...

Buttercup Games: Further Dashboarding Techniques (Part 9)

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...

Buttercup Games: Further Dashboarding Techniques (Part 8)

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...