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

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
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...