Splunk Search

variable passed into subsequent search

rberman
Explorer

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
Explorer

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
Explorer

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
Explorer

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!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...