Splunk Search

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

jebabin
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,

0 Karma
1 Solution

masonmorales
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

View solution in original post

masonmorales
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

masonmorales
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.

0 Karma

jebabin
Engager

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

0 Karma

masonmorales
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?

0 Karma

jebabin
Engager

yes sorry and F

0 Karma

juvetm
Communicator

ok did u use this the transaction command to try this

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...