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.
@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, 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 ]
@niketnilay I tried both the approaches but to no avail.
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.
@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.
Thank you, that works
I really appreciate your help with this.