Splunk Search

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

wcooper003
Communicator

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?

valerie_tan
Path Finder

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

For older versions, try....

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

wcooper003
Communicator

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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" .

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