topic Re: How to use "where" and "not in" and "like" in one query in Splunk Search

How to use "where" and "not in" and "like" in one query?

JyotiP — Thu, 17 Nov 2022 14:38:04 GMT

I have the following query :
sourcetype="docker" AppDomain=Eos Level=INFO Message="Eos request calculated" | eval Val_Request_Data_Fetch_RefData=Round((Eos_Request_Data_Fetch_MarketData/1000),1)

Which have 3 host like perf, castle, local.

I want to use the above query bust excluding host like castle and local

sourcetype="docker" AppDomain=Eos Level=INFO Message="Eos request calculated" | eval Val_Request_Data_Fetch_RefData=Round((Eos_Request_Data_Fetch_MarketData/1000),1) | where host NOT like 'castle' AND 'local' ??
Will it work

Re: How to use "where" and "not in" and "like" in one query

HiroshiSatoh — Wed, 13 Sep 2017 09:41:56 GMT

Try this!

sourcetype="docker" AppDomain=Eos Level=INFO Message="Eos request calculated" NOT (host='castle' OR  host='local')

This is also possible.

NOT (host='*castle*' OR  host='*local*')

Re: How to use "where" and "not in" and "like" in one query

cmerriman — Wed, 13 Sep 2017 11:37:32 GMT

to wildcard NOT, you can do like what @HiroshiSatoh mentioned and go with

sourcetype="docker" AppDomain=Eos Level=INFO Message="Eos request calculated" NOT (host=*castle* OR  host=*local* OR host=*perf*)| eval Val_Request_Data_Fetch_RefData=Round((Eos_Request_Data_Fetch_MarketData/1000),1)

which will remove the hosts that contain perf, castle, or local from the base search
or if you need to remove it later on in the search, after doing evals/stats with it, perhaps, using where and like would be like this:

...|where NOT like(host,"%perf%") AND NOT like(host,"%castle%") AND NOT like(host,"%local%")

Re: How to use "where" and "not in" and "like" in one query

abbasimani — Thu, 17 Nov 2022 05:51:49 GMT

You can also use:

NOT (host IN (*castle*,*local*))

So full query will be something like this:

sourcetype="docker" AppDomain=Eos Level=INFO Message="Eos request calculated" NOT (host IN (*castle*,*local*))