topic Re: How do I Compare field values between Lookup Table and Sourcetype and find the Delta in Splunk Search

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

SplunkDash — Fri, 10 Mar 2023 06:05:33 GMT

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.

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

bowesmana — Fri, 10 Mar 2023 06:09:56 GMT

Try this

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

SplunkDash — Fri, 10 Mar 2023 06:29:48 GMT

Hello,

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

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

bowesmana — Fri, 10 Mar 2023 06:44:55 GMT

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

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

SplunkDash — Fri, 10 Mar 2023 06:44:59 GMT

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.

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

bowesmana — Sat, 11 Mar 2023 01:43:07 GMT

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

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

SplunkDash — Wed, 19 Jul 2023 20:55:25 GMT

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!