Archive

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

View solution in original post

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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.