Is there an option to add text filter with different type of matching in dashboard?

[LIS]

Hi colleagues,

Is there option to add text filter with different type of matching, like it is in Excel (containts, doesnt contain, begginig with, end with, etc.) to dashboard.

[tscroggins · Accepted solution]

@LIS 

Hi,

In classic dashboards, you can use form inputs combined with the where command:

<form version="1.1">
  <label>LIS_filters</label>
  <fieldset submitButton="true">
    <input type="dropdown" token="filter_tok">
      <label>Filter</label>
      <choice value="eq">Equals</choice>
      <choice value="ne">Does Not Equal</choice>
      <choice value="co">Contains</choice>
      <choice value="nc">Does Not Contain</choice>
      <choice value="bw">Begins With</choice>
      <choice value="ew">Ends With</choice>
      <choice value="li">Length is</choice>
      <choice value="lg">Length is greater than</choice>
      <choice value="ll">Length is less than</choice>
      <choice value="nb">Does Not Begin With</choice>
      <choice value="nw">Does Not End With</choice>
    </input>
    <input type="text" token="field_tok">
      <label>Field</label>
    </input>
    <input type="text" token="value_tok">
      <label>Value</label>
    </input>
    <input type="time" token="time_tok">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <event>
        <search>
          <query>index=main
``` assign value_tok to field to prevent errors in tonumber() when parsing literals ```
| eval _value_tok="$value_tok$" 
| where coalesce(
    case(
    "$filter_tok$"=="eq", '$field_tok$'==_value_tok,
    "$filter_tok$"=="ne", '$field_tok$'!=_value_tok,
    "$filter_tok$"=="co", like('$field_tok$', "%"._value_tok."%"),
    "$filter_tok$"=="nc", NOT like('$field_tok$', "%"._value_tok."%"),
    "$filter_tok$"=="bw", like('$field_tok$', _value_tok."%"),
    "$filter_tok$"=="ew", like('$field_tok$', "%"._value_tok),
    "$filter_tok$"=="li", len('$field_tok$')==coalesce(tonumber(trim(_value_tok)), -1),
    "$filter_tok$"=="lg", len('$field_tok$')&gt;coalesce(tonumber(trim(_value_tok)), 9223372036854775807),
    "$filter_tok$"=="ll", len('$field_tok$')&lt;coalesce(tonumber(trim(_value_tok)), -1),
    "$filter_tok$"=="nb", NOT like('$field_tok$', _value_tok."%"),
    "$filter_tok$"=="nw", NOT like('$field_tok$', "%"._value_tok)
    ),
    false())</query>
          <earliest>$time_tok.earliest$</earliest>
          <latest>$time_tok.latest$</latest>
        </search>
        <option name="list.drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </event>
    </panel>
  </row>
</form>

The logic is embedded in the case() function. For example, "host" "Begins With" "spl" is translated to the following search:

index=main
``` assign value_tok to field to prevent errors in tonumber() when parsing literals ```
| eval _value_tok="spl" 
| where coalesce(
    case(
    "bw"=="eq", 'host'==_value_tok,
    "bw"=="ne", 'host'!=_value_tok,
    "bw"=="co", like('host', "%"._value_tok."%"),
    "bw"=="nc", NOT like('host', "%"._value_tok."%"),
    "bw"=="bw", like('host', _value_tok."%"),
    "bw"=="ew", like('host', "%"._value_tok),
    "bw"=="li", len('host')==coalesce(tonumber(trim(_value_tok)), -1),
    "bw"=="lg", len('host')>coalesce(tonumber(trim(_value_tok)), 9223372036854775807),
    "bw"=="ll", len('host')<coalesce(tonumber(trim(_value_tok)), -1),
    "bw"=="nb", NOT like('host', _value_tok."%"),
    "bw"=="nw", NOT like('host', "%"._value_tok)
    ),
    false())

The tonumber() function returns NULL if a field value is not a number but returns an error if a literal value is not a number. To prevent search errors, I've assigned the field value token, value_tok, to a new field, _value_tok. If this field name conflicts with a field name in your events, you can change the name and update the where command.

[PickleRick]

You're sooooo overcomplicating it 😉

Tokens are just inserted in the apropriate placeholders. Use it.

<form version="1.1" theme="dark">
<label>test2</label>
<fieldset submitButton="false">
<input type="dropdown" token="cond">
<label>condition</label>
<choice value="&gt;">greater than</choice>
<choice value="&lt;">lower than</choice>
</input>
</fieldset>
<row>
<panel>
<table>
<search>
<query>| tstats count where index=* by sourcetype 
| where count $cond$ 10</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
</search>
<option name="drilldown">none</option>
</table>
</panel>
</row>
</form>

[tscroggins]

@PickleRick 

Indeed! But my goal was to be explanatory, not clever. 😉 There may be other requirements to meet with respect to Excel-like (or non-Excel-like) comparisons, input validation, etc. If I were coding "greater than" and "[less] than," for example, I'd want to know if the comparison was lexical or numerical? If lexical, what collation should be used? And so on....

[LIS]

Hi @tscroggins ,

It is so perfect and simple(since it was invented by somebody😊). Thank you very much!

I have adjusted it a bit to fit to my case, because have faced "waiting for input"  in my table. 

<set token="field_tok"></set>
<set token="value_tok"></set>

<input type="dropdown" token="filter_tok" >
  <label>Filter</label>
  <choice value="eq">Equals</choice>
  <choice value="ne">Does Not Equal</choice>
  <choice value="co">Contains</choice>
  <choice value="nc">Does Not Contain</choice>
  <choice value="bw">Begins With</choice>
  <choice value="ew">Ends With</choice>
  <choice value="li">Length is</choice>
  <choice value="lg">Length is greater than</choice>
  <choice value="ll">Length is less than</choice>
  <choice value="nb">Does Not Begin With</choice>
  <choice value="nw">Does Not End With</choice>
  <choice value="ps">Pls select</choice>
   <default>Pls select</default>
</input>

     | where coalesce(
case(
"$filter_tok$"=="eq", '$field_tok$'==_value_tok,
"$filter_tok$"=="ne", '$field_tok$'!=_value_tok,
"$filter_tok$"=="co", like('$field_tok$', "%"._value_tok."%"),
"$filter_tok$"=="nc", NOT like('$field_tok$', "%"._value_tok."%"),
"$filter_tok$"=="bw", like('$field_tok$', _value_tok."%"),
"$filter_tok$"=="ew", like('$field_tok$', "%"._value_tok),
"$filter_tok$"=="li", len('$field_tok$')==coalesce(tonumber(trim(_value_tok)), -1),
"$filter_tok$"=="lg", len('$field_tok$')>coalesce(tonumber(trim(_value_tok)), 9223372036854775807),
"$filter_tok$"=="ll", len('$field_tok$')<coalesce(tonumber(trim(_value_tok)), -1),
"$filter_tok$"=="nb", NOT like('$field_tok$', _value_tok."%"),
"$filter_tok$"=="nw", NOT like('$field_tok$', "%"._value_tok),
"$filter_tok$"=="ps", (1==1)
),
false())