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