topic Re: How can I do a basic "IN" command in Splunk? in Splunk Search

How can I do a basic "IN" command in Splunk?

hanriv0001 — Tue, 29 Sep 2020 21:56:46 GMT

I am trying to accomplish a simple "IN" command in Splunk, basically by filtering the result to show only those entries which have an entry for their "product_id" number in a another tables "product_number" attribute.

But Splunk throws an error saying: "Error in 'search' command: Unable to parse the search: Comparator 'IN' has an invalid term on the right hand side: NOT"

sourcetype= Order  product_id IN [ search host=product | table product_number] | stats count by order_id

Any help in understanding what I am doing wrong would be of great.

Re: How can I do a basic "IN" command in Splunk?

niketn — Thu, 08 Nov 2018 19:04:14 GMT

@hanriv0001, try the following search:

sourcetype= Order product_id IN 
    [ search host=product
    | stats values(product_number) as product_number
    | eval product_number="(\"".replace(mvjoin(product_number,","),",","\",\"")."\")"
    | rename product_number as search] 
| stats count by order_id

Following is a run anywhere example based on Splunk's _internal index, on lines similar to above:

index=_internal sourcetype=splunkd log_level IN 
    [ search index=_internal sourcetype=splunkd 
    | stats values(log_level) as log_level 
    | eval log_level="(\"".replace(mvjoin(log_level,","),",","\",\"")."\")"
    | rename log_level as search]

Based on your use case, ideally, you should be trying the following approach using format command rather than IN clause,

sourcetype=Order 
    [ search host=product
    | dedup product_number
    | rename product_number as product_id
    | format] 
| stats count by order_id

Following is a run anywhere search based on Splunk's _internal index on lines similar to example above:

index=_internal sourcetype=splunkd 
    [ search index=_internal sourcetype=splunkd 
    | dedup log_level 
    | table log_level 
    | format ]

Re: How can I do a basic "IN" command in Splunk?

hanriv0001 — Thu, 08 Nov 2018 20:28:55 GMT

@niketnilay I tried both the approaches but to no avail.

Re: How can I do a basic "IN" command in Splunk?

hanriv0001 — Tue, 29 Sep 2020 21:56:59 GMT

Its the way the sub-search returns the result is the problem
It returns a key value pair which like "(product_number = " 123") OR (product_number = " 345")"
which destroys the syntax for the IN clause.

Re: How can I do a basic "IN" command in Splunk?

niketn — Tue, 29 Sep 2020 22:01:41 GMT

@hanriv0001 , two things which you missed in the second answer:

1) | rename product_number as product_id ensures that format command prepares search filter as (product_id = " 123") OR (product_id = " 345") and not that with product_name.

2) Since the filters are prepared as several OR conditions you don't need product_id IN in your search.

PS: If you want to use product_id IN in your main search, you would need to prepare comma separated values like ("123","345"). For which option 1 should work.

Have you tried the run anywhere searches? The values of log_level in _internal index can be INFO ERROR or WARN. So you can append | stats count by log_level to any of your searches to test the same.

Re: How can I do a basic "IN" command in Splunk?

hanriv0001 — Thu, 08 Nov 2018 22:18:14 GMT

Thank you, that works
I really appreciate your help with this.