Splunk Search

## Is there a better way to filter on multiple values than my current search?

Engager

Hello,

I have the following event entries:

``````NAME=A;VAL=15;
NAME=A;VAL=5;
NAME=B;VAL=15;
NAME=C;VAL=15;
NAME=C;VAL=15;
NAME=D;VAL=5;
NAME=E;VAL=5;
NAME=E;VAL=;
NAME=F;VAL=5
NAME=F;VAL=2
``````

What I want is to get the list of `NAME` for which there is not at least one VAL > 10
So, I should get D and E

I can't do directly`| where VAL <= 10 OR isnull(VAL)` as I would get `A` as well.
If I do `| stats values(VAL) by NAME`, I'm then restricted to mv* function in my where clause and I don't see a mv function that could be of help here.

So I tried `| eval OK=if(VAL <= 10 OR isnull(VAL),0,1)` which gives

``````NAME=A;VAL=15;OK=1
NAME=A;VAL=5;OK=0
NAME=B;VAL=15;OK=1
NAME=C;VAL=15;OK=1
NAME=C;VAL=15;OK=1
NAME=D;VAL=5;OK=0
NAME=E;VAL=5;OK=0
NAME=E;VAL=;OK=0
NAME=F;VAL=5;OK=0
NAME=F;VAL=2;OK=0
``````

And now I need the list of `NAME` for which there is not at least one OK = 1
Then I can do `| stats sum(OK) as CNT_OK by NAME | where CNT_OK = 0`

So I have `| eval OK=if(VAL <= 10 OR isnull(VAL),0,1) | stats sum(OK) as CNT_OK, max(VAL) by NAME | where CNT_OK = 0`

My question is, do you see a better way to achieve this ?

Regards,

Tags (4)
1 Solution
Influencer

You could use a subsearch. The subsearch is evaluated first and is treated as a boolean AND to your first search.

``````search ...your_base_search... NOT [search ...your_base_search... | stats max(VAL) as MAX by NAME | search MAX>10 | table NAME] | table NAME VAL
``````
Influencer

You could use a subsearch. The subsearch is evaluated first and is treated as a boolean AND to your first search.

``````search ...your_base_search... NOT [search ...your_base_search... | stats max(VAL) as MAX by NAME | search MAX>10 | table NAME] | table NAME VAL
``````
Influencer

The subsearch I gave will find the maximum value by NAME, filer to results >10, then produce a list of NAMEs. The NOT against the subsearch causes those NAMEs to be excluded from your results.

Engager

Thanks, it's right that I did not need CNT_OK, and could filter directly on MAX after stats!

Influencer

What I want is to get the list of "NAME" for which there is not at least one VAL > 10
So, I should get D and E

...and F?

Engager

yes sorry and F

Communicator

ok did u use this the transaction command to try this  