Splunk Search

How to search with "IN" to produce same results as "OR"?

balcv
Contributor

I have a search with a bunch of OR's and I wanted to replace it with "IN" however I do not get the same results.

My working search is :

index="mimecast" (Sender="*gmail.com" OR Sender="*@outlook.com" OR Sender="*@my.com" OR Sender="*@mail.ru") AND (Subject="*Availab*" OR Subject="*Urgent*" OR Subject="*Hello*" OR Subject="*Request*")
| stats count by Sender,Subject
| where count >=4
| sort -Count

My modified, non-working search is:

index="mimecast" Sender in ("*gmail.com","*@outlook.com","*@my.com","*@mail.ru") AND Subject in ("*Available*","*Urgent*","*Availability*","*Hello*","*Request*")
| stats count by Sender,Subject
| where count >=4
| sort -Count

Can anyone explain why the first version works and the second does not? I've tried using brackets () in different places etc byt cannot get any results to be returned.

Thanks!

0 Karma
1 Solution

adonio
Ultra Champion

try this: with CAPITAL IN

index="mimecast" Sender IN("*gmail.com","*@outlook.com","*@my.com","*@mail.ru") AND Subject IN("*Available*","*Urgent*","*Availability*","*Hello*","*Request*")
 | stats count by Sender,Subject
 | where count >=4
 | sort -Count

elaborated blog post here:
https://www.splunk.com/blog/2019/05/08/smooth-operator-searching-for-multiple-field-values.html

hope it helps

View solution in original post

adonio
Ultra Champion

try this: with CAPITAL IN

index="mimecast" Sender IN("*gmail.com","*@outlook.com","*@my.com","*@mail.ru") AND Subject IN("*Available*","*Urgent*","*Availability*","*Hello*","*Request*")
 | stats count by Sender,Subject
 | where count >=4
 | sort -Count

elaborated blog post here:
https://www.splunk.com/blog/2019/05/08/smooth-operator-searching-for-multiple-field-values.html

hope it helps

View solution in original post

balcv
Contributor

That worked. Thanks @adonio . I didn't realize that these were case sensitive so I'll need to be more careful in the future.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!