topic Re: Field in Lookup Table with Multiple Values in Splunk Search

Field in Lookup Table with Multiple Values

alan20854 — Fri, 01 Jul 2016 15:28:05 GMT

In one of the columns of my lookup table, host, some values have two hosts rather than just one.

For example, my table might look like this
app,environment,host
app1,env1,host1
app1,env2,host1 OR host2
...

In my dashboard panel, I am writing a query that would search for all events with the selected environment and host. However, for the ones with two hosts, the search query only searches for the first host. How would I be able to search for both?

Re: Field in Lookup Table with Multiple Values

richgalloway — Fri, 01 Jul 2016 15:47:23 GMT

If you have control over the lookup table, consider a separate row for the extra host.

app,environment,host
app1,env1,host1
app1,env2,host1
app1,env2,host2

Re: Field in Lookup Table with Multiple Values

alan20854 — Fri, 01 Jul 2016 15:52:18 GMT

Hi Rich,

I am using the lookup table to populate my dropdowns, but I do not want to create another dropdown for host. Is there a way to search for both hosts now that they are in separate rows?

Re: Field in Lookup Table with Multiple Values

richgalloway — Fri, 01 Jul 2016 16:15:36 GMT

I didn't get the part where you were populating a dropdown. This answer won't help with that.

Re: Field in Lookup Table with Multiple Values

sundareshr — Fri, 01 Jul 2016 16:57:47 GMT

You can use dynamic tokens. Something like this

<input type=dropdown>
<change>
<eval token="hosts">replace($value$, " OR ", " OR host=")</eval>

Re: Field in Lookup Table with Multiple Values

alan20854 — Tue, 05 Jul 2016 17:20:32 GMT

Would this be the same for if I just had static options in my dropdown?

Re: Field in Lookup Table with Multiple Values

alan20854 — Tue, 05 Jul 2016 17:35:04 GMT

I tried to do what you suggested, but it didn't seem to change my search query when I opened it up in search. Here is the XML:

  <input type="dropdown" token="env" searchWhenChanged="true">
  <label>Environment</label>
  <choice value="host1 OR host2 OR host3">PROD</choice>
  <choice value="host1 OR host4">QA</choice>
  <choice value="host5">QI1</choice>
  <choice value="host5 OR host6">QI2</choice>
  <choice value="host7">QC1</choice>
  <change>
    <eval token="hosts">replace($value$, " OR ", " OR host=")</eval>
  </change>
</input>

Re: Field in Lookup Table with Multiple Values

alan20854 — Tue, 05 Jul 2016 18:00:08 GMT

And my search query is: host = $env$ ...

Re: Field in Lookup Table with Multiple Values

sundareshr — Tue, 05 Jul 2016 18:03:25 GMT

If these are static values, your best option would be change the values to

   <choice value="host=host1 OR host=host2 OR host=host3">PROD</choice>
   <choice value="host=host1 OR host=host4">QA</choice>
   <choice value="host=host5">QI1</choice>
   <choice value="host=host5 OR host=host6">QI2</choice>
   <choice value="host=host7">QC1</choice>

If these are dynamically populated, you could add the host= in the query that is populating the dropdown.

The other option is to use tags in your data and in the dropdowns
http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Defineandusetags

Re: Field in Lookup Table with Multiple Values

alan20854 — Tue, 05 Jul 2016 18:06:48 GMT

Thanks for the help!

Re: Field in Lookup Table with Multiple Values

jbillings — Mon, 02 Mar 2020 15:29:40 GMT

Way late to the game, but for anyone that is looking for something like this, you may try insert this in your search. It will make the host field multivalue based on the comma.
| inputlookup
| makemv delim="," host