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

[jebabin]

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,

[masonmorales · Accepted solution]

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

[masonmorales]

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.

[jebabin]

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

[masonmorales]

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?

[jebabin]

yes sorry and F

[juvetm]

ok did u use this the transaction command to try this