topic Re: How to Filter out records if they match multiple criteria? in Splunk Search

How to filter out records if they match multiple criteria?

ahadalioglu — Tue, 17 May 2022 13:03:15 GMT

Hi there,

I want to filter out some records if they match multiple criteria, for example:

host service state
=================
h1 s1 stopped
h1 s2 running
h2 s1 stopped
h3 s1 running
h4 s1 running
h4 s2 running
h4 s3 stopped

So I need to filter out only hosts that have multiple services and the host with s1 service is stopped. The output should be like this:

host service state
=================
h2 s1 stopped
h3 s1 running
h4 s1 running
h4 s2 running
h4 s3 stopped

Explanation: First two records with h1 are gone as it had multiple services and the one with s1 was stopped. Also, h2 is still in the output because it's running only one service - s1, so it shouldn't be filtered out even though it's stopped.

Hope I could explain my problem,

Huge thanks in advance

Re: How to Filter out records if they match multiple criteria?

richgalloway — Mon, 16 May 2022 15:58:19 GMT

The eventstats command should help.

... | eventstats dc(service) as serviceCount by host | where (serviceCount > 1 AND service="s1" AND state="stopped")

Re: How to Filter out records if they match multiple criteria?

ahadalioglu — Tue, 17 May 2022 12:51:25 GMT

Thanks for the solution, it partially does what I need but still, I need some adjustments as:

1. this keeps all records that are required to be filtered out (removed from the results), I guess it's enough to add NOT after where, am I right?

2. another issue is that it filters out only the records where it's stopped, but is supposed to remove all records of the same host(s), if you look at the first sample result, I've removed the first 2 records, not only the first one with stopped (2nd record with running also should be gone if it has service other than s1)

3. this was not mentioned in my example, but it appeared that there are some other related service names too that contain s1 (e.g. s1v1 or v1s1), so dc(service) doesn't help here as I need something like like(service,"%s1%") (or any better way) within dc() which seems doesn't work

Let me create a new sample and the required results:

GIVEN:
host service state
=================
h0 s1v1 stopped
h0 v2s1 stopped
h1 s1 stopped
h1 s2 running
h2 s1 stopped
h3 s1 running
h4 s1 running
h4 s2 running
h4 s3 stopped

RESULTS SHOULD BE:
host service state
=================
h0 s1v1 stopped
h0 v2s1 stopped
h2 s1 stopped
h3 s1 running
h4 s1 running
h4 s2 running
h4 s3 stopped

Explanation: h0's are still there as the services are still related to s1 (contains s1) and they don't have any services other than s1, but h1'a are both gone (not only the first h1 with stopped)

Huge thanks in advance for your help, time, and efforts!

Re: How to Filter out records if they match multiple criteria?

PickleRick — Tue, 17 May 2022 11:59:57 GMT

About your first question - close, but not quite.

There is a significant difference between a

field!=value

condition and

NOT field=value

one (and similar constructions like a<=b and NOT a>b).

The condition "field!=value" matches all events where there is a field called "field" (has a non-null value) and this value is not equal to "value". But the "NOT field=value" matches all events where "field=value" condition is not fulfilled which means that the field called "field" can have a value different than "value" but also can have no value at all. So there might be a subset of your results which would match one of those conditions but not the other. It's up to you to decide which way you want it.

There is also a bit of a difference regarding matching multivalued fields. If you have a field called "field" which has two walues - "value1" and "value2", the condition

field!="value1"

will - a bit counterintuitively - match said field because the search conditions are matched against each value of the multivalued field separately. Therefore the "value2" value of that field will match that condition. But if you did a condition of

NOT field="value1"

the event would not match since the field="value1" condition would be fulfilled on the "value1" value and the negative condition would not be fulfilled.

A bit complicated, I know, but it's worth getting used to it to avoid surprises.

And about your initial question. Well, you should do something close to what @richgalloway already showed but with one small detail

<your search>
| eval is_s1_stopped=if(service="s1" AND state="stopped",1,0)
| eventstats values(is_s1_stopped) by host
| where NOT is_s1_stopped=1

Re: How to Filter out records if they match multiple criteria?

ahadalioglu — Tue, 17 May 2022 13:37:36 GMT

Thanks for the clarification, I'd definitely need to take this into account beyond this...

Concerning the solution, unfortunately it still keeps on the screen some records:

This is what is the result:
host service state
====================
h0 s1v1 stopped
h0 v2s1 stopped
h1 s2 running
h2 s1 stopped
h3 s1 running
h4 s1 running
h4 s2 running
h4 s3 stopped

bold and italic record is still there - which should be also gone because it's the same host which we filtered out with stopped s1 service

Hope we are very close to the solution 🙂
Thanks for the prompt collaboration, guys!

Re: How to Filter out records if they match multiple criteria?

PickleRick — Tue, 17 May 2022 13:40:30 GMT

Ahh... stupid mistake. I did the eventstats but didn't rename it so effectively I filtered on the wrong field afterwards.

<your search>
| eval is_s1_stopped=if(service="s1" AND state="stopped",1,0)
| eventstats values(is_s1_stopped) as is_s1_stopped by host
| where NOT is_s1_stopped=1

Re: How to Filter out records if they match multiple criteria?

ahadalioglu — Tue, 17 May 2022 14:10:36 GMT

Actually, I tried both: the way you corrected, this time it did almost nothing - the same initial result (filtering didn't work at all); also renamed it as a different field name (thought last where should check the is_s1_stopped field, but none of them works 😞

Re: How to filter out records if they match multiple criteria?

somesoni2 — Tue, 17 May 2022 14:18:13 GMT

Try something like this

Your current search giving fields host, service, state | eventstats dc(service) as serviceCount sum(eval(if(service="s1" AND state="stopped",1,0))) as s1_stopped by host | where NOT (serviceCount > 1 AND s1_stopped>0)

Re: How to Filter out records if they match multiple criteria?

PickleRick — Tue, 17 May 2022 15:15:50 GMT

Another simple mistake 🙂

The last command should not be "where" but "search". Where needs a bit different syntax (it could probably be written using "where" but it's way easier switching to search).

Re: How to filter out records if they match multiple criteria?

ahadalioglu — Wed, 18 May 2022 17:44:56 GMT

Huge thanks, it worked.

Can you please kindly explain the combination that starts with sum() ?

Thanks to others as well, you taught me some other stuff too, really appreciate it!

Best regards

Re: How to filter out records if they match multiple criteria?

somesoni2 — Wed, 18 May 2022 19:43:24 GMT

sum(eval(if(service="s1" AND state="stopped",1,0)))

For every host, check if the service name is s1 and state is stopped (on the same row), then add 1, otherwise add 0 for all other rows. So after running stats for all rows for a host, if sum (or the field s1_stopped)>0 which means that host has service s1 with state=stopped. Other filter serviceCount >1 means that to only filter when there are more than one service for that host.

CyberArk Splunk Report Issue

ismayilahmadov — Thu, 19 May 2022 15:08:04 GMT

Hello,

It could be very helpful to give advice for this issue.

The daily Splunk report containing the list of failed logon attempts to Password Vault does not contain any results. This has been a blank report for awhile now. I usually review the vault italog for the information, but would like to see how to get the daily report fixed.

Thanks