topic Re: case function -- why can't I operate on the results of a case function? in Splunk Search

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

pgiffd — Fri, 23 Jun 2017 21:35:09 GMT

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.

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

horsefez — Fri, 23 Jun 2017 21:45:12 GMT

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!

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

acharlieh — Fri, 23 Jun 2017 22:24:33 GMT

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.

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

DalJeanis — Fri, 23 Jun 2017 23:31:26 GMT

@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)