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

[graju89]

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

[somesoni2]

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 ]

[graju89]

It worked thanks.

[gcusello · Accepted solution]

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

[wmyersas]

This is substantially faster in most cases:

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

[graju89]

It worked. Thanks.