Splunk Search

How to compare two fields from two different searches and display results field not exist?

graju89
Path Finder

I am running 2 different searches and have to compare the each value in one field with the values in the other field. The display result should show field A values which does not exist in field B.

given data:

Field A:
1111
2222
2424
3333
4444

Field B:
3333
1111
4444
3344
Results should be something like this table:

Field A --
2222
2424

Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi graju89,
if results of the second search are less than 50,000, you can run something like this:

index=indexA NOT [search index=indexB | rename FieldB AS FieldA | fields FieldA ]
| dedup FieldA
| sort FieldA
| table FieldA

If instead you could have more than 50,000 results in the second search, you cannot use subsearches and you have to run a search like this:

index=indexA OR index=indexB 
| rename FieldB AS FieldA 
| stats dc(index) AS num_indexes values(index) AS index BY FieldA
| where num_indexes=1 AND index=indexA
| table FieldA

Ciao.
Giuseppe

View solution in original post

0 Karma

somesoni2
Revered Legend

Assuming your FieldA and FieldB is single valued field, try something like this

your search generating FieldA
| where NOT [search which generates FieldB |rename FieldB as FieldA ]
0 Karma

graju89
Path Finder

It worked thanks.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi graju89,
if results of the second search are less than 50,000, you can run something like this:

index=indexA NOT [search index=indexB | rename FieldB AS FieldA | fields FieldA ]
| dedup FieldA
| sort FieldA
| table FieldA

If instead you could have more than 50,000 results in the second search, you cannot use subsearches and you have to run a search like this:

index=indexA OR index=indexB 
| rename FieldB AS FieldA 
| stats dc(index) AS num_indexes values(index) AS index BY FieldA
| where num_indexes=1 AND index=indexA
| table FieldA

Ciao.
Giuseppe

0 Karma

wmyersas
Builder

This is substantially faster in most cases:

index=ndxA
| search NOT
[ search index=ndxB
  | stats count by FieldB
  | fields - count
]
0 Karma

graju89
Path Finder

It worked. Thanks.

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...