Splunk Search

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

DATT
Path Finder

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:

 

DateHostStatus
2024-07-22host1NEW
2024-07-22host2NEW
2024-07-23host1ACTIVE
2024-07-23host2ACTIVE
2024-07-24host1ACTIVE
2024-07-24host2ACTIVE
2024-07-25host1FIXED
2024-07-25host2ACTIVE
2024-07-26host2ACTIVE
2024-07-27host2ACTIVE
2024-07-28host2ACTIVE
2024-07-29host2ACTIVE

 

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?

0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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

DateHostStatus
2024-07-22host2NEW
2024-07-23host2ACTIVE
2024-07-24host2ACTIVE
2024-07-25host2ACTIVE
2024-07-26host2ACTIVE
2024-07-27host2ACTIVE
2024-07-28host2ACTIVE
2024-07-29host2ACTIVE

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 ```

 

View solution in original post

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust
<something> Status!=FIXED
0 Karma

DATT
Path Finder

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

DateHostStatus
2024-07-22host2NEW
2024-07-23host2ACTIVE
2024-07-24host2ACTIVE
2024-07-25host2ACTIVE
2024-07-26host2ACTIVE
2024-07-27host2ACTIVE
2024-07-28host2ACTIVE
2024-07-29host2ACTIVE

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 ```

 

Tags (1)
0 Karma

DATT
Path Finder

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.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Celebrating Fast Lane: 2025 Authorized Learning Partner of the Year

At .conf25, Splunk proudly recognized Fast Lane as the 2025 Authorized Learning Partner of the Year. This ...

Tech Talk Recap | Mastering Threat Hunting

Mastering Threat HuntingDive into the world of threat hunting, exploring the key differences between ...

Observability for AI Applications: Troubleshooting Latency

If you’re working with proprietary company data, you’re probably going to have a locally hosted LLM or many ...