Splunk Search

case function -- why can't I operate on the results of a case function?

pgiffd
New Member

case function -- why can't I operate on the results of a case function? After the eval case function, I got 100 rows. I want to eliminate 30 of them using a standard where clause. (ex. where fieldA = "X" should remove all rows where fieldA = "Y".) All the rows remain. How can I exclude rows? I need to execute 5 cases, and then at the end I want to remove what wasn't found in any of them.

Tags (2)
0 Karma

acharlieh
Influencer

This was getting long for a comment...

(ex. where fieldA = "X" should remove all rows where fieldA = "Y".)

This is not actually correct... unset/null fields, and multivalued fields make this logic a bit more complex. |where fieldA="X" keeps all rows where a value of the field is X... some of those rows could have fieldA="Y" as well.

I'm going to use this runanywhere query for an example base query:

| makeresults count=12 | streamstats count | eval fieldA=case(count%4=1,"X",count%4=2,"Y",count%4=3,split("X,Y",","))

We have 3 rows (1,5,9) where fieldA="X", 3 rows (2,6,10) where fieldA="Y", 3 rows (3,7,11) where fieldA is both, and 3 rows (4,8,12) where fieldA is null.

<basesearch> | where fieldA="X"

actually gives 6 rows (1,5,9, and 3,7,11), on half of these, fieldA="Y"

<basesearch> | where NOT fieldA="Y"

which gives you 6 rows (1,5,9 and 4,8,12)... on the latter 3 now, fieldA doesn't exist, so fieldA="Y" is false

<basesearch> | where fieldA!="Y"

which gives you only 3 rows (1,5,9) (or where fieldA equals a value that isn't Y)

<basesearch> | where isnull(fieldA)

which gives you only the 3 rows (4,8,12) where fieldA isn't any value at all

This should also show that using eval with case to generate a field and then filter on it with where is indeed possible, and the last one might be what you're looking for, but as @pyro_wood mentions to solve your particular case we likely need more specific information.

DalJeanis
Legend

@charlieh gave you a great answer and a demo why.

Just in case you need to understand what to do about it, here's one way...

| eval mynewfield=case(firsttest=1,"pass1",  secondtest="whatever","pass2",   .... lasttest="ok","lastpass",   true(),null()   )
| where isnotnull(mynewfield)
0 Karma

horsefez
Motivator

Hi,

it's not very clear what you are trying to do, or whats the actual problem.
Please post your query and some sample events.
Thanks!

0 Karma
Get Updates on the Splunk Community!

Join Us for Splunk University and Get Your Bootcamp Game On!

If you know, you know! Splunk University is the vibe this summer so register today for bootcamps galore ...

.conf24 | Learning Tracks for Security, Observability, Platform, and Developers!

.conf24 is taking place at The Venetian in Las Vegas from June 11 - 14. Continue reading to learn about the ...

Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...