How can I filter all events on one field by a value in another field?

[DATT]

We pull weekly vulnerability reports from Splunk associated with our Qualys data.  I am trying to filter out all records associated with a hostname if the status field equals "Fixed".

The data for a couple hosts might look like this:

Date	Host	Status
2024-07-22	host1	NEW
2024-07-22	host2	NEW
2024-07-23	host1	ACTIVE
2024-07-23	host2	ACTIVE
2024-07-24	host1	ACTIVE
2024-07-24	host2	ACTIVE
2024-07-25	host1	FIXED
2024-07-25	host2	ACTIVE
2024-07-26	host2	ACTIVE
2024-07-27	host2	ACTIVE
2024-07-28	host2	ACTIVE
2024-07-29	host2	ACTIVE

Both host1 and host2 discover a new vulnerability on 7-22.  On 7-23, the status for both flip to "ACTIVE".  On 7-25, however, host1 is now showing a FIXED status. Host2 remains vulnerable through the remaining date range of the report.

Since host1 fixed the vulnerability during the timeframe, how could I go about removing all host1 events based on the status field being equal to "Fixed" on the most recent data pull?

[yuanliu]

<something> Status!=FIXED

[DATT]

This isn't what I'm looking for. This eliminates just the event with a fixed status. I need to remove all of the host1 events because one of them has a status of fixed.

[yuanliu · Accepted solution]

My apologies for not reading the question carefully.  eventstats is your friend.

| eventstats values(Status) by Host
| where NOT "FIXED" IN ('values(Status)')
| fields - "values(Status)"

Here, I am breaking out of my usual pattern to use a semantic filter.  For economy, you can also use the side effect of Splunk's equality on multivalue:

| eventstats values(Status) by Host
| where 'values(Status)' != "FIXED"
| fields - "values(Status)"

Either way, you get

Date	Host	Status
2024-07-22	host2	NEW
2024-07-23	host2	ACTIVE
2024-07-24	host2	ACTIVE
2024-07-25	host2	ACTIVE
2024-07-26	host2	ACTIVE
2024-07-27	host2	ACTIVE
2024-07-28	host2	ACTIVE
2024-07-29	host2	ACTIVE

Here is an emulation you can play with and compare with real data

| makeresults format=csv data="Date,	Host,	Status
2024-07-22,	host1,	NEW
2024-07-22,	host2,	NEW
2024-07-23,	host1,	ACTIVE
2024-07-23,	host2,	ACTIVE
2024-07-24,	host1,	ACTIVE
2024-07-24,	host2,	ACTIVE
2024-07-25,	host1,	FIXED
2024-07-25,	host2,	ACTIVE
2024-07-26,	host2,	ACTIVE
2024-07-27,	host2,	ACTIVE
2024-07-28,	host2,	ACTIVE
2024-07-29,	host2,	ACTIVE"
``` data emulation above ```

[DATT]

This is great, thank you.  I have struggled with eventstats in the past, and this is the first time I can remember seeing a use case for it that made sense to me.

[PickleRick]

My three cents

| where NOT a in b

or

| where NOT b=a

(as you can do with  multivalued fields)

is NOT the same as

| where a!=b

The first form filters out all results where value a appears anywhere in the field b - as one of the values in mulitivalued field whereas the second form keeps all results which have at least one value in field b which is different than a.

Also results with empty field b are treated differently.