Splunk Search

How to filter out records if they match multiple criteria?

ahadalioglu
Explorer

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

Labels (2)
0 Karma
1 Solution

somesoni2
Revered Legend

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)

View solution in original post

somesoni2
Revered Legend

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)

ahadalioglu
Explorer

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

0 Karma

somesoni2
Revered Legend
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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The eventstats command should help.

... | eventstats dc(service) as serviceCount by host
| where (serviceCount > 1 AND service="s1" AND state="stopped")
---
If this reply helps you, Karma would be appreciated.

ahadalioglu
Explorer

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!

0 Karma

PickleRick
Ultra Champion

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

ahadalioglu
Explorer

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!

0 Karma

PickleRick
Ultra Champion

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
0 Karma

ahadalioglu
Explorer

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 😞

0 Karma

PickleRick
Ultra Champion

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

 

0 Karma

ismayilahmadov
Loves-to-Learn

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

 

0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...