https://community.splunk.com/t5/Splunk-Search/Comparing-two-fields-in-different-format-from-two-different/m-p/540302#M152844 <P>Hi <a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/231592">@amsagg</a>&nbsp;Try Something like below,</P><P>index=stream_dns dest_asset_tag=*dns OR dest_asset_tag=A<BR />| rex field=fieldB "(?&lt;fieldB&gt;[^\.]+)"&nbsp; ## To extract first portion to match with your lookup filed value<BR />| table fieldB<BR />| eval Flag="1"<BR />| append<BR />[| inputlookup dnslookup.csv<BR />| table fieldA<BR />| rename fieldA as fieldB<BR />| eval Flag="1"]<BR />| eventstats sum(Flag) Flag by fieldB<BR />| dedup fieldB<BR />| where Flag=1 ##If the field value exists in both index &amp; lookup, the flag will be set to 2. Hence filtering to 1<BR />| table fieldB</P> Wed, 17 Feb 2021 19:22:39 GMT Kwip 2021-02-17T19:22:39Z https://community.splunk.com/t5/Splunk-Search/Comparing-two-fields-in-different-format-from-two-different/m-p/540184#M152792 <P>Hi Everyone,<BR /><BR />I am trying to use&nbsp; a lookup table and an index to get an output as a comparison of two fields from two different sources<BR /><BR />lookup has a field that is in the format like this (fieldA)<BR />aaa<BR />ddd<BR />fff<BR /><BR /><BR />index has a field that is in the format like this (fieldB)<BR /><BR />aaa.ccc.com<BR />ddd.ccc.com<BR />eee.ccc.com<BR /><BR />index=stream_dns dest_asset_tag=*dns OR dest_asset_tag=A | append<BR />[| inputlookup dnslookup.csv | table fieldA | rename fieldA as fieldB ] | stats count by&nbsp; dest, fieldB<BR /><BR />The result should look like the missing fields from comparison of fieldA and fieldB in this format<BR />eee<BR />fff<BR /><BR /><BR /></P> Wed, 17 Feb 2021 02:18:47 GMT https://community.splunk.com/t5/Splunk-Search/Comparing-two-fields-in-different-format-from-two-different/m-p/540184#M152792 amsagg 2021-02-17T02:18:47Z https://community.splunk.com/t5/Splunk-Search/Comparing-two-fields-in-different-format-from-two-different/m-p/540206#M152801 <P>Firstly, you should convert aaa.ccc.com to aaa otherwise they will not match</P><P>Secondly, if you only want the mismatches, and not any detail, you could dedup fieldB before the append</P><P>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</P> Wed, 17 Feb 2021 08:33:59 GMT https://community.splunk.com/t5/Splunk-Search/Comparing-two-fields-in-different-format-from-two-different/m-p/540206#M152801 ITWhisperer 2021-02-17T08:33:59Z https://community.splunk.com/t5/Splunk-Search/Comparing-two-fields-in-different-format-from-two-different/m-p/540302#M152844 <P>Hi <a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/231592">@amsagg</a>&nbsp;Try Something like below,</P><P>index=stream_dns dest_asset_tag=*dns OR dest_asset_tag=A<BR />| rex field=fieldB "(?&lt;fieldB&gt;[^\.]+)"&nbsp; ## To extract first portion to match with your lookup filed value<BR />| table fieldB<BR />| eval Flag="1"<BR />| append<BR />[| inputlookup dnslookup.csv<BR />| table fieldA<BR />| rename fieldA as fieldB<BR />| eval Flag="1"]<BR />| eventstats sum(Flag) Flag by fieldB<BR />| dedup fieldB<BR />| where Flag=1 ##If the field value exists in both index &amp; lookup, the flag will be set to 2. Hence filtering to 1<BR />| table fieldB</P> Wed, 17 Feb 2021 19:22:39 GMT https://community.splunk.com/t5/Splunk-Search/Comparing-two-fields-in-different-format-from-two-different/m-p/540302#M152844 Kwip 2021-02-17T19:22:39Z