Splunk Search

variable passed into subsequent search

rberman
Path Finder

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

Labels (1)
0 Karma
1 Solution

rberman
Path Finder

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

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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.

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

rberman
Path Finder

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

rberman
Path Finder

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$)\""
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...