Why am I getting error "Unknown search command 'sourcetype'" using a subsearch in a where command?

[maximus_reborn]

I want to do something like the below command but it is giving me an error.

sourcetype=SplunkKafka_messaging | spath input=msg_body | where passenger_count > [sourcetype=SplunkRabbitMQ_messaging | spath input=msg_body | stats avg(passenger_count)]

Error:

Unknown search command 'sourcetype'.

Can anyone let me know how to achieve the objective? My aim is to extract the events where passenger_count is greater then the average of that column.

This will happen in real time.

[somesoni2 · Accepted solution]

Try this

sourcetype=SplunkKafka_messaging | spath input=msg_body | where passenger_count > [search sourcetype=SplunkRabbitMQ_messaging | spath input=msg_body | stats avg(passenger_count) as avg | return $avg]

OR

sourcetype=SplunkKafka_messaging | spath input=msg_body | where passenger_count > [search sourcetype=SplunkRabbitMQ_messaging | spath input=msg_body | stats avg(passenger_count) as query]

[maximus_reborn]

Thanks! It did work.

[philippiq]

try to insert 'search' before sourcetype

[NOUMSSI]

Hi try this:

sourcetype=SplunkKafka_messaging | spath input=msg_body | where passenger_count > [search sourcetype=SplunkRabbitMQ_messaging | spath input=msg_body | stats avg(passenger_count)]

[NOUMSSI]

Dont forget to accept my answer if you were satisfied

[maximus_reborn]

I am getting 2 errors:
1. Error reading runtime settings: File :/usr/local/splunk/var/run/splunk/dispatch/subsearch_1464015531.154_1464015531.1/runtime.csv does not exist
2. ERROR dispatchRunner - RunDispatch::runDispatchThread threw error: Error in 'where' command: Typechecking failed. The '>' operator received different types