Dashboards & Visualizations

How to replace WHERE style filter (e.g. search or tstats) with PIVOT compatible filter

Motivator

Hi,

I have a lot of dashboards currently using standard search or tstats with a WHERE filter. The token for the filter is populated by a multi-select that sets the value to something like index=foo OR index=bla. However, in the same dashboard I have | pivot panels as well, and these use a different FILTER syntax, but I want the same filter from the token applied to both search/tstats and pivot style searches.

Pivot would require the following syntax:

FILTER index inList (foo,bar)

I've tried the following to get this to work:

In the multi-select

<change><eval token="pivot_style_filter">replace(replace(old_style_filter,"index=", "")," OR ",",")</eval></change>

However this only replaces the last entered value of the multi-select (last checked item)

I've also tried doing this with a macro in-line in the | pivot search, but the macro isn't expanded in this syntax:

| pivot ... FILTER index inList (`replace_old_style_filter`). 

The backticks and everything is passed into the search log sent to the indexers.

Does anyone have an excellent solution to this matter? I don't really get why pivot can't accept the WHERE style filtering as well as FILTER style filtering.

My temporary workaround was to hack out the generated tstats command out of the search job inspector and use that for the searches, but this breaks drilldowns and prevents real-time searches from being done. It is also more work to maintain since you cannot simply reverse a tstats search back to a pivot search just like that.

1 Solution

Champion

It is likely going to be challenging to modify the token inline with the pivot command, because pivot does not support inline subsearches like other commands do.

Without adding Javascript extensions, the simplest solution I can think of is adding a global search to modify the token and set a new token. Assuming you are running 6.4 or newer, you would add the following to the top of your dashboard

<search>
 <query>| makeresults | eval token=replace(replace($old_style_filter|s$,"index=","")," OR ",",") | table token</query>
 <done>
    <set token="pivot_filter">$result.token$</set>
  </done>

And then you would use the new token "pivot_filter" in the your pivot SPL like the following:

| pivot ... FILTER index in $pivot_filter$ ....

View solution in original post

0 Karma

Builder

This may have changed, but the inList command does not work on 6.5.2. in needs to be used instead and it only works on string fields.

0 Karma

Motivator

BINGO! The following works:

  <search>
   <query>| makeresults | eval token=replace(replace($old_style_filter|s$,"index=","")," OR ",",") | table token</query>
   <done>
      <set token="pivot_filter">$result.token$</set>
    </done>
 </search>

Note the singular "result", not plural "results". The latter would not work.

Thank you!

Champion

It is likely going to be challenging to modify the token inline with the pivot command, because pivot does not support inline subsearches like other commands do.

Without adding Javascript extensions, the simplest solution I can think of is adding a global search to modify the token and set a new token. Assuming you are running 6.4 or newer, you would add the following to the top of your dashboard

<search>
 <query>| makeresults | eval token=replace(replace($old_style_filter|s$,"index=","")," OR ",",") | table token</query>
 <done>
    <set token="pivot_filter">$result.token$</set>
  </done>

And then you would use the new token "pivot_filter" in the your pivot SPL like the following:

| pivot ... FILTER index in $pivot_filter$ ....

View solution in original post

0 Karma

Motivator

Great tip, however the only values I end up in my testing are:

  • $pivot_filter$ (indicating the token isn't set)
  • $results.token$

I cannot get it to actually do the replacement. I've tested with $result.token$, $results.token$, 'result.token', 'results.token' as well as a hard coded "index=foo OR index=bar" inside the replace function.

Your example provided an extra ) so I removed that as well to get the right syntax

I'm on Splunk 6.5.1

0 Karma

Champion

Sorry for the typos. Try this.

  <search>
   <query>| makeresults | eval token=replace(replace($old_style_filter|s$,"index=", "")," OR ",",") | table token</query>
  <done>
      <set token="pivot_filter">$results.token$</set>
    </done>
 </search>
0 Karma

Motivator

Yours is accepted. It would be great if you could edit it with the correct syntax as described in my Answer below.

0 Karma

Champion

Here is the corrected version:

  <search>
    <query>| makeresults | eval token=replace(replace($old_style_filter|s$,"index=","")," OR ",",") | table token</query>
    <done>
       <set token="pivot_filter">$result.token$</set>
     </done>
  </search>