Splunk Search

Comparing two fields in different format from two different sources

amsagg
Observer

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


Labels (5)
Tags (1)
0 Karma

Kwip
Contributor

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Enhance Your Splunk App Development: New Tools & Support

UCC FrameworkAdd-on Builder has been around for quite some time. It helps build Splunk apps faster, but it ...

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...