How do you exclude a dropdown value from a search if default/no value selected?

[syg6]

I have a dashboard with 3 dropdowns, let's call call them country, state and city, each with its corresponding token ($country$, $state$ and $city$). They are not inter-dependent, and the default, static value for each dropdown is "*".

My data looks like this:

timestamp   country  state  city
2019-03-29  USA
2019-03-29            NY
2019-03-29                  NYC

In other words, each entry can have a value for, at most, 1 of these columns—either country, state or city. For example, country and state can't both have data in any given entry. Nor can state and city, country and city, etc.

My (naive) query was something like this:

index=* sourcetype=xxx result=000 AND country = $country$ AND state = $state$ AND city =$city$

The problem is, If no value is selected from any dropdown (so the default value "*" is used), no data is being displayed. Why? From what I gather, null values (state and city in the 1st line, country and city in the 2nd line, etc.) are not included in "*". In other words, "country = $country$" would return data on its own for line 1, but since the search also has "state = $state$ AND city =$city$", and those columns are null, no data is shown.

Is there a way to exclude the tokens from the query, if no value has been selected? In other words, if the non-default value for country is selected, the query would be

index=* sourcetype=xxx result=000 AND country = $country$

and if the non-default value for state were selected, the query would be

index=* sourcetype=xxx result=000 AND state = $state$

and if the non-default value for city were selected, the query would be

index=* sourcetype=xxx result=000 AND city =$city$

I was also considering using if/then/else or a case statement somehow ... but I can't seem to get it work work.

Any help much appreciated, I'm a Splunk noob.

[renjith_nair]

@syg6,

Try this run anywhere example and let me know what changes you need

<form>
  <label>Dropdown</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="country">
      <label>Country</label>
      <choice value="USA">USA</choice>
      <choice value="">All</choice>
      <default></default>
      <change>
        <condition label="All">
          <set token="country"></set>
        </condition>
        <condition>
          <set token="country">AND country="$value$"</set>
        </condition>
      </change>
    </input>
    <input type="dropdown" token="state">
      <label>State</label>
      <choice value="">All</choice>
      <choice value="NY">NY</choice>
      <default></default>
      <change>
        <condition label="All">
          <set token="state"></set>
        </condition>
        <condition>
          <set token="state">AND state="$value$"</set>
        </condition>
      </change>      
    </input>
    <input type="dropdown" token="city">
      <label>City</label>
      <choice value="">All</choice>
      <choice value="NYC">NYC</choice>
      <default></default>
      <change>
        <condition label="All">
          <set token="city"></set>
        </condition>
        <condition>
          <set token="city">AND city="$value$"</set>
        </condition>    
      </change>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>|makeresults|eval country ="USA"|append [|makeresults |eval state="NY"]|append [|makeresults|eval city="NYC"]|eval random="something"
          |search random="something" $country$ $state$ $city$</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
      </table>
    </panel>
  </row>
</form>

For e.g.

- What should be displayed if all dropdowns are selected as default (All) ?
- What should be displayed if more than one non-default value is selected ? for e.g. if country and state are selected

[syg6]

That does seem to work properly. I think I get what you did here ... You basically took the logic that I thought would go in the query (conditionally add country=$country$, state=$state$ or city=$city$), and put it in the logic of each dropdown. Very clever! I had no idea you could do that!

As to your questions ... I've thought about it some more and I think the logical thing to do here would be to have 2 dropdowns. One in which you have the 3 things you can search by: country, state or city, and in the other, possible values for country, state or city, depending on what you selected in the first dropdown.

Once you've chose a value from the first and second dropdown, then you perform the search. I think I will be able to apply your trick to that scenario.

I'll write here how it went. Many thanks again for the help!

[syg6]

That worked like a charm. Thanks again!

I still don't really get why "" doesn't work for empty/null columns. I'm not sure if internally splunk treats the value sent by the dropdown as a regular expression ... if it does "" should work, because it means "0 or more". But something tells me it does not treat it as a regular expression.

At any rate, if you look at the url when using your solution, instead of

/my_dashboard?form.country=*&form.state=*&form.city=*

it sends

/my_dashboard?form.country=&form.state=&form.city=&

In other words, country, state and city are blank. And the results are correct.

[renjith_nair]

@syg6,
It doesnt matter what value inside "All" label since we are matching against label. So you could replace "" with "*" in the dropdown value so that your url also will have *. I had put "" just for example

ie below also should work

<input type="dropdown" token="country">
       <label>Country</label>
       <choice value="USA">USA</choice>
       <choice value="*">All</choice>

[syg6]

Right, I understand that. In fact I am not using *, I am using empty string ("") as the default/nothing selected value.

What I meant to say was, is there no symbol you can send from the dropdown that would return results that have the column corresponding to the dropdown empty/null?

The only thing that works is doing what you suggest, essentially saying "country=nothing". What I was wondering is, is there no "country=anything" ?

Regardless, my question is academic, since your solution works fine.

[renjith_nair]

@syg6, in this case, its null() and NULL is neither equal to a value nor unequal to it, so any comparison involving NULL is neither true nor false.

You can test it by this

|makeresults|eval something="xyz",nothing=""|search nothing=*

you should get one row because * matches "" (empty)

|makeresults|eval something="xyz",nothing=""|eval nothing=if(nothing=="",null(),null())|search nothing=*

Here I have replaced value of nothing to null() and hence * is not matching them

However, you can try with OR NOT field_name="*" to search for null values and incorporate into your search