Using multi-select input1 results to drive multi-select input2 menu in dashboard studio

[learningmode]

Hello,

I've been struggling with this for way too long and it seems like it should be a fairly simple thing to do. I'm hoping someone here can help.

SCENARIO:

BaseSearch1 generates a table of [host, IsBlocked, IsHF, IsIDX] fields where host=string and the remaining are boolean (0 or 1). This represents a list of hosts that have either seen a queue block since [earliest=$global_time.earliest$ latest=$global_time.latest$], are a HF/DS, or are named ^idx-*. I will put the base search at the end of this posting to make it easier to read.

MultiSelect1 uses ChainSearch1 linked to BaseSearch1 to present only the hostnames from BaseSearch1 where IsBlocked=1.

Query:
     | search IsBlocked=1
     | table host

Token:$tok_MultiSelect1$

OBJECTIVE:

Create a datasource called ChainSearch2 for MultiSelect2 that will filter the following query using the hosts listed in $tok_MultiSelect1$, essentially like this

Query:

index=_internal (sourcetype=metrics OR sourcetype=splunkd) group=queue name=* host IN($tok_MultiSelect1$)
| fields name
| dedup name sortby name
| table name

ISSUES:

Problem1 (Solved, basis for Problem2):

Even though the base search feeding the MultiSelect1 chain search ends with " | table host IsHF IsBlocked IsIDX", I see the fields ["_time","host"] when using this query to evaluate $tok_MultiSearch1$ by sending it to a table:

CODE:
| makeresults
| eval host_list="$tok_MultiSearch1$"

...which makes that output invalid for "host IN()"

This was overcome with this query:

| makeresults
| eval host_list="$tok_MultiSearch1$"
| fields - _time
| makemv delim="," host_list
| mvexpand host_list
| fields - _mkv_child
| eval quoted_host="\"" . host_list . "\""
| stats list(quoted_host) as quoted_hosts
| eval host_filter=mvjoin(quoted_hosts, ",")
| fields - quoted_hosts

The result is that now host_filter = "server1","server2"

Problem2 (unsolved):

This query WILL return the data I am looking for

index=_internal earliest=-15m group=queue host IN ("server1","server2")
| fields name
| dedup name sortby name
| table name

However, if I use $host_filter for the server values (shown above in Problem1), even though it's a comma-delimited list of quoted server names, I get an empty output:

| makeresults
| eval host_list="$tok_MultiSearch1$"
| fields - _time 
| makemv delim="," host_list
| mvexpand host_list
| fields - _mkv_child
| eval quoted_host="\"" . host_list . "\""
| stats list(quoted_host) as quoted_hosts
| eval host_filter=mvjoin(quoted_hosts, ",") < where I get "server1","server2"
| fields - quoted_hosts
| search index=_internal earliest=-15m group=queue host IN ($host_filter)
| fields name
| dedup name sortby name
| stats values(name) as Queues

 I cannot seem to get $tok_MultiSearch1$ used properly in ChainSearch2.

Again, I am guessing that this is going to be something simple that I've overlooked, it's just not coming to me.

Thanks in advance for any help that can be offered!

[learningmode]

Hi Victor,

And there is the little thing I was missing...understanding that the multi-select was looking for a list and I was sending an array. I would probably never had figured that out. Thank you so much for your quick replies and working solution! 

To anyone reading this later, adding that table to the dashboard and duplicating the search results for the multi-select object to visually inspect the format and values of the token being passed was very helpful. I highly recommend it as a troubleshooting step.

Final working query:

index=_internal earliest=-15m group=queue name=*
[
| makeresults
| eval host_list="$tok_MultiSelect1$"
| makemv delim="," host_list
| eval host_list=mvmap(host_list, "\"" . trim(host_list) . "\"")
| eval search = "host IN (" . mvjoin(host_list, ",") . ")"
| return $search
]
| fields name
| dedup name sortby name
| stats count by name
| rename name as Queues
| table Queues

Regards

[victor_menezes]

Hi @learningmode,

Let me try to help you here... As per your description the solution should be really straightforward so I'm sure it is just something small left behing.
Starting with your last search there, the token is like that exactly as you pasted? If so, you missed a "$" after "$host_filter":

...
| fields - quoted_hosts
| search index=_internal earliest=-15m group=queue host IN ($host_filter)
| fields name
...

Did you noticed that?

[learningmode]

Hi Victor,

Thanks for the quick reply. Using $host_filter$ references a token, which doesn't exist. The input for this query is "$tok_MultiSearch1$".

In this case, $host_filter is being used as a variable intending to represent the output of 
"| eval host_filter=mvjoin(quoted_hosts, ",")" from earlier in that same query.

Now, whether or not $host_filter is a "legal" variable and can be used in this way is very much part of this mystery.

Regards

[victor_menezes]

@learningmode I'm sorry, I completely overlooked that, you are right, you are using $host_list as a parameter to get the field content, not a token value...

Try this instead:

index=_internal earliest=-15m group=queue name=*
[
  | makeresults
  | eval host_list="$tok_MultiSearch1$"
  | makemv delim="," host_list
  | eval host_list=mvmap(host_list, "\"" . trim(host_list) . "\"")
  | eval search = "host IN (" . mvjoin(host_list, ",") . ")"
  | return $search
]
| fields name
| dedup name sortby name
| stats values(name) as Queues

What I'm suggesting here:
Run the makeresults that gets the token value to run as subsearch, which takes priority and runs first. The "return" clause will return a field to the parent search context, that can then be accessed and evaluated properly.

Give it a try and see if that works. Also, you can customize the "search" field's return format by editing line 7 to adhere to your expectations.

[learningmode]

Hi Victor,

I recognize that approach and I know I've tried it before and failed. However, knowing that it's likely something I am overlooking, I tried this again. This time though, I set up a table and mirrored your query to that too (trust, but verify inline with the actual token)

What I didn't expect to see is that in the TABLE, the list of queues ARE being returned (see below). My problem was actually that MultiSelect2 seems to be refusing to publish them. This is true regardless if I select "Queues(array)" for the menu label and value fields in the MultiSelect2 properties.

I deleted and recreated MultiSelect2, same problem. Perhaps this has been the issue all along:

Same exact query but sending the output to a table instead:

Queues

aggqueue

auditqueue

fschangemanager_queue

httpinputq

indexqueue

nullqueue

parsingqueue

rulesetqueue

splunktcpin

tcpout

typingqueue

Not solved, but making great progress in that it looks like the real problem may have just shown itself. If this is the case, I can only imagine how many attempts copilot and I have made before that actually should have worked...

Any thoughts on this new direction?

I appreciate your help with this!

[victor_menezes · Accepted solution]

Okay, let's try to replace stats values with stats count and remove the count field.. Like this:

...
| dedup name sortby name
| stats count by name
| rename name as Queues
| table Queues

I could simulate the same behavior as you are facing, and this workaround seems to have worked for me:

Let's see if that works.

I thought about it as the DS is showing "Queues (Array)" so understanding the return as an array while dropdown expects individual values, and if you think about it is actually expected and it makes sense.
So it is easier to "transform" it as multiple events rather than unpack the MV field that stats values create, but I guess it would work also.