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,
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
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
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.
Thanks, it's right that I did not need CNT_OK, and could filter directly on MAX after stats!
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?
yes sorry and F
ok did u use this the transaction command to try this