topic Re: variable passed into subsequent search in Splunk Search

variable passed into subsequent search

rberman — Thu, 17 Jun 2021 15:00:29 GMT

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

Re: variable passed into subsequent search

richgalloway — Thu, 17 Jun 2021 18:55:02 GMT

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.

Re: variable passed into subsequent search

rberman — Mon, 21 Jun 2021 14:43:54 GMT

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.

Re: variable passed into subsequent search

richgalloway — Mon, 21 Jun 2021 15:27:29 GMT

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

Re: variable passed into subsequent search

rberman — Mon, 21 Jun 2021 16:56:07 GMT

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-subsearch-to/m-p/438337

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$)\""