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

[hanriv0001]

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.

[niketn · Accepted solution]

@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 ]

[hanriv0001]

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

[hanriv0001]

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.

[niketn]

@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.

[hanriv0001]

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