variable passed into subsequent search

[rberman]

Is it possible to use the value derived from one search and pass it to another search? 

For example, I have a search and create a comma separated list of values ids_list:

<first search> | stats values(id) as temp_ids | eval ids_list=mvjoin(temp_ids, ",")

I'd like to use ids_list in a subsequent search. For example:

<first search that returns a list of ids> | stats values(id) as temp_ids | eval ids_list=mvjoin(temp_ids, ",") | search  [ | dbxquery query="SELECT t.id, t.description FROM 'database_name'.'table_name' t WHERE t.id IN (ids_list)" connection="connection_name"] 

[richgalloway]

Yes, that can be done.  Run the "first" search in a subsearch (inside square brackets) so it runs first.  The results of the subsearch will become part of the main search, which runs after the subsearch completes.

| dbxquery query="SELECT t.id, t.description FROM 'database_name'.'table_name' t WHERE t.id IN [<first search that returns a list of ids> | stats values(id) as temp_ids | eval ids_list=mvjoin(temp_ids, ",") | return $temp_ids]" connection="connection_name"

I've not used a subsearch within dbquery so YMMV.

[rberman]

Thank you so much for answering me @richgalloway! I tried your suggestion and the subsearch doesn't run because it is inside the quotes of the query parameter as in

| dbxquery query="some select text [<your suggested subquery>]" connection="connection_name"

Is there a way to get around the quotes?  I have been trying 

| dbxquery query="SELECT t.id, t.description FROM 'database_name'.'table_name' t WHERE t.id IN(?)" connection="connection_name" params=[<first search that returns a list of ids> | stats values(id) as temp_ids | eval ids_list=mvjoin(temp_ids, ",") | return $temp_ids]

but that hasn't worked for me.

[richgalloway]

I was afraid the subsearch wouldn't work in dbquery.  Sorry, but I don't have a workaround.

[rberman · Accepted solution]

I did some searching around and was able to get it to work via the answer on this thread: https://community.splunk.com/t5/Splunk-Search/Splunk-dbxquery-to-call-stored-procedure-with-subsearc...

my resulting query is:

<first search> | stats values(id) as temp_ids | eval ids_list=mvjoin(temp_ids, ",") |
map search="| dbxquery connection=\"connection_name\" query=\"SELECT t.id, t.description FROM database_name.table_name t WHERE t.id IN ($ids_list$)\""