Dashboards & Visualizations

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

mikaelbje
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

rjthibod
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

helge
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

mikaelbje
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!

rjthibod
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$ ....
0 Karma

mikaelbje
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

rjthibod
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

mikaelbje
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

rjthibod
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>
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...