topic How to filter multiple values with pivot command - is the 'in' operator broken? in Splunk Search

How to filter multiple values with pivot command - is the 'in' operator broken?

wcooper003 — Thu, 15 Jun 2017 20:18:47 GMT

Based on the Splunk pivot command documentation, one should be able to use:

| pivot .....
   splitrow fieldname
   filter fieldname in ("some text1", "some text2")

However, this doesn't work for me - it only returns the first value in the list, not both of them. Even if I remove the quotes from the strings which seems like it works ok, it still just return the first value. However, this works:

| pivot .....
   splitrow fieldname
   filter fieldname contains some

This isn't ideal because it returns a lot of stuff I don't want. So the question is how do I do a simple OR logical to filter on multiple values with the pivot command?

Does the pivot command have a bug where the 'in' comparison operator doesn't work?

Re: How to filter multiple values with pivot command - is the 'in' operator broken?

DalJeanis — Fri, 16 Jun 2017 03:34:45 GMT

what version of splunk are you on? I believe "in" is brand new.

For older versions, try....

 (fieldname ="some text1" OR fieldname ="some text2")

Re: How to filter multiple values with pivot command - is the 'in' operator broken?

wcooper003 — Fri, 16 Jun 2017 12:59:19 GMT

Thanks for the response. Using 6.5.2 soon to be on 6.6.1.

I tried that as:

| pivot .....
    splitrow fieldname
    filter (fieldname ="some text1" OR fieldname="some text2")

But that didn't execute.

Re: How to filter multiple values with pivot command - is the 'in' operator broken?

DalJeanis — Sat, 17 Jun 2017 18:25:19 GMT

Interesting... no, 6.5 should be fine for in.

Documentation for FILTER is kind of bizarre. The = operator doesn't work with strings, you have to use is?

Okay, I added the bug tag, because this is apparently a bug, but here's a couple more shots in the dark for potential workarounds. If either one works, it will be a minor miracle.

A) try using FILTER and IN in all caps. The doc shows it in lower case, but what the heck.

B) try filter fieldname is "some text" OR fieldname is "some text2" .

Re: How to filter multiple values with pivot command - is the 'in' operator broken?

valerie_tan — Sat, 10 Mar 2018 05:47:56 GMT

I finally found a working answer! I am using Splunk 7.0 and above.

This was what I did cause I couldn't find any working answer for passing multiselect tokens into Pivot FILTER command in the search query. (For a better understanding of how the SPL works)
Step 1: Make a pivot table and add a filter using "is in list", add it as a inline search report into a dashboard.
Step 2: Open the search query in Edit mode. TADA. you can see the cotde that they used.
Step 3: Breathe a huge sigh of relief. The answer is simpler than what has been given ...

Using the Multiselect token:
Set the following:
token prefix and suffix as ( and )
values prefix and suffix with "
delimiter with ,

("value1", "value2", value3"...) <- this is what u should see in their preview.

**In search query""

Simply type
FILTER XXX in $xxx_token|s$
Note that the token escape is the life saver. the values originally r kinda messed up.

Please let me know if it works for you guys.