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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...