Comparing two fields in different format from two different sources

[amsagg]

Hi Everyone,

I am trying to use  a lookup table and an index to get an output as a comparison of two fields from two different sources

lookup has a field that is in the format like this (fieldA)
aaa
ddd
fff


index has a field that is in the format like this (fieldB)

aaa.ccc.com
ddd.ccc.com
eee.ccc.com

index=stream_dns dest_asset_tag=*dns OR dest_asset_tag=A | append
[| inputlookup dnslookup.csv | table fieldA | rename fieldA as fieldB ] | stats count by  dest, fieldB

The result should look like the missing fields from comparison of fieldA and fieldB in this format
eee
fff

[Kwip]

Hi @amsagg Try Something like below,

index=stream_dns dest_asset_tag=*dns OR dest_asset_tag=A
| rex field=fieldB "(?<fieldB>[^\.]+)"  ## To extract first portion to match with your lookup filed value
| table fieldB
| eval Flag="1"
| append
[| inputlookup dnslookup.csv
| table fieldA
| rename fieldA as fieldB
| eval Flag="1"]
| eventstats sum(Flag) Flag by fieldB
| dedup fieldB
| where Flag=1 ##If the field value exists in both index & lookup, the flag will be set to 2. Hence filtering to 1
| table fieldB

[ITWhisperer]

Firstly, you should convert aaa.ccc.com to aaa otherwise they will not match

Secondly, if you only want the mismatches, and not any detail, you could dedup fieldB before the append

Then, when you count by fieldB, if your count is greater than 1, it appears in both then index and the lookup, otherwise it is a difference