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"]
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$)\""
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.
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.
I was afraid the subsearch wouldn't work in dbquery. Sorry, but I don't have a workaround.
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$)\""