Getting Data In

Pivot table filter flexibility?

sspencer_splunk
Splunk Employee
Splunk Employee

I have indexed a dataset that contains a collection of customer names, their purchases, their addresses, and other various bits of information that you might expect to see in a CRM database for a web store. I've also created a data model, a variety of objects within that data model, and I've assigned "auto-extracted" attributes to each of those objects. (This feature is awesome, BTW!)

When I create a pivot, I've discovered that I can't figure out how to filter the pivot on more than one value of a particular attribute. For example, I'd like to be able to filter my pivot down to customers that reside in North Dakota, Hawaii, and Washington DC. Is it possible to create a pivot filter (without creating eval fields or using other GUI acrobatics outside the pivot interface itself) that will filter results for multiple values of a field (e.g. ND, HI, and/or DC)? When I configure multiple filters, they appear to be logically ANDed together. The result is that no entries are returned. What I'm looking for is the ability to logically OR those filters together.

Thanks!

Tags (2)
0 Karma
1 Solution

mattness
Splunk Employee
Splunk Employee

Have you tried setting up an object that uses a constraint search to ensure that it only includes events where customer = ND OR HI OR DC? As long as the customer field exists as an auto-extracted attribute in the data this should be doable. Then you could just build a pivot based on that object.

View solution in original post

snoobzilla
Builder

Learn to use tstats to access the backend gets you OR filtering with tokens against accelerated data.

0 Karma

mattness
Splunk Employee
Splunk Employee

Have you tried setting up an object that uses a constraint search to ensure that it only includes events where customer = ND OR HI OR DC? As long as the customer field exists as an auto-extracted attribute in the data this should be doable. Then you could just build a pivot based on that object.

matthieu_araman
Communicator

Would have liked to use this also.
This is to implement a dashboard with :
- search built with underlying pivot search
- input forms

being able for the user to give several values as filter like he would do if he was using the search bar (many choices, the user can type)

Will try to work around by adding a subfilter afterwards but that's less efficient and transparent.

0 Karma

dasveruckte
New Member

I know this thread is old but since I just had the same question I wanted to post my work around. Basically I added the filter on the base search. I know it may not be practical for all users but it worked for my use case.

0 Karma

snoobzilla
Builder

I noticed that they have added options to pivot table filters. One that may fit here is "is in the list" which matches to a comma separated list. You can also use contains.

The option I ended up going with in many cases is learning | tstats syntax then you can do OR. Inspect a filtered pivot search and look for tstats... then probably change prestats to false, rename node.* as *, and you are off.

0 Karma

mattness
Splunk Employee
Splunk Employee

I documented it at the note in the "Configure a filter element" subtopic: http://docs.splunk.com/Documentation/Splunk/latest/Pivot/UsingthePivotvisualizationeditor#Configure_...

0 Karma

dvb
Path Finder

@ mattness: Where in the documentation did you add that?
I'm searching how to use filters with OR, for being able to use checkboxes to drive my dashboard panel (pivot searches)

0 Karma

mattness
Splunk Employee
Splunk Employee

Unfortunately Pivot is currently limited by an inability to set up OR operations with its filters. I'll update the Pivot docs to make this clear.

snoobzilla
Builder

Argh! Please fix this!

jtrujillo
Path Finder

You sir. Get an upbeat! PLEASE FIX THIS!!

0 Karma

sspencer_splunk
Splunk Employee
Splunk Employee

Yes, I could do that, but that would only work for that one permutation of customer locations. It doesn't scale to any degree, unless I'm misinterpreting your response.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...