How do I Compare field values between Lookup Table and Sourcetype and find the Delta

[SplunkDash]

Hello,

I am facing issues to find delta.

I have:

Lookup Table: testaccount_holder.csv

2 Field names in Lookup: account_no and cell

index=test Sourcetype =test_account

2 Field names :  account_no and cell

Now, need to compare Lookup table with  sourcetype using these 2 fields and find all the records/rows which are exist in Lookup table but not in sourcetype. This comparison is based on these 2 fields.

Any recommendations will be highly appreciated. Thank you so much.

[bowesmana · Accepted solution]

Try this

index=test sourcetype=test_account
| stats count by account_no cell
| lookup testaccount_holder.csv account_no cell OUTPUT account_no as found
| append [
  | inputlookup testaccount_holder.csv
]
| stats values(found) as found by account_no cell
| where isnull(found)

[SplunkDash]

Hello @bowesmana 

Delta created using following logic is not giving the right output.  Output from that delta I found basically what we can get if you run: 

| inputlookup testaccount_holder.csv

Any recommendations would be appreciated. Thank you!

[SplunkDash]

Hello,

I have one more question related to this. I was trying show other fields which are present in Lookup Table and was using (| table account_no cell name address) at the end, but not showing up. Any recommendation will be highly appreciated. Thank you again.

index=test sourcetype=test_account
| stats count by account_no cell
| lookup testaccount_holder.csv account_no cell OUTPUT account_no as found
| append [
| inputlookup testaccount_holder.csv
]
| stats values(found) as found by account_no cell
| where isnull(found)| table account_no cell name address

[bowesmana]

After the stats command you will only have fields that are part of the stats, so in this case, found, account_no and cell. So, if you want to carry through address do this using the modified append and stats.

index=test sourcetype=test_account
| stats count by account_no cell
| lookup testaccount_holder.csv account_no cell OUTPUT account_no as found
``` So in the search above, you will have 3 fields
    account_no, cell and found
```
| append [
  | inputlookup testaccount_holder.csv
  ``` Now specify the fields you want from the lookup to pass through to the end result ```
  | fields account_no cell name address
]
``` and this stats command using wildcards, will carry through ANY of the fields you retain above ```
| stats values(*) as * by account_no cell
| where isnull(found)
| table account_no cell name address

[SplunkDash]

Hello,

Thank you so much for your quick response, truly appreciate it. Your SPL code is working as expected, AWESOME!!!

[bowesmana]

Great! You will find this is a common pattern referred to as 'proving the negative', i.e. when the data does NOT contain what you are looking for, so you do the pattern of

1. Search Data
2. Set an indicator that it's present in data
3. Append the expected source of truth
4. Resolve the duplicates
5. Validate what is not present