topic Re: Creating Lookup table from another dynamic Lookup table in Knowledge Management

Creating Lookup table from another dynamic Lookup table

Splunk1ngSpree — Wed, 23 Sep 2020 12:03:46 GMT

Hi Splunkers!

I have a Lookup table (lookup_table_1) that pulls external CSV data periodically and updates itself (script). I need to add a couple of columns that I could update manually along with the data that is automatic updated.

I was thinking creating another lookup (lookup_table_2) that is generated by querying (lookup_table_1) and adding 2 extra columns (given lookup_table_2 already has fields a, b, c, d, e created):

Re: Creating Lookup table from another dynamic Lookup table

ITWhisperer — Wed, 23 Sep 2020 12:22:33 GMT

Would this work?

Essentially, add the extra fields for "known" Ids, then add all the known rows (in case some are missing from table 1), dedup the rows and write away

Re: Creating Lookup table from another dynamic Lookup table

Splunk1ngSpree — Wed, 23 Sep 2020 13:35:49 GMT

Thanks @ITWhisperer , I added all dynamic fields to the lookup command:

This does work appending new rows from lookup_1 to lookup_2, and preserving static fields. However if a row is removed from lookup_1 it does not remove it from lookup_2. Is there a way to replace all dynamic data from lookup_1 in lookup_to instead of only appending new (but not removing from lookup_2 if removed from lookup_1).

Thanks! Half way there 🙂

Re: Creating Lookup table from another dynamic Lookup table

ITWhisperer — Wed, 23 Sep 2020 13:52:37 GMT

I am not sure I understand what you are asking for - what do you mean by replacing the dynamic data? If you don't want the data that is in table 2 unless it has corresponding data in table 1, then don't append table 2. There is also a dedup by Id, is this one of the match fields?

Re: Creating Lookup table from another dynamic Lookup table

Splunk1ngSpree — Wed, 23 Sep 2020 15:43:38 GMT

Basically, lookup_1 is constantly changing, new rows are added and removed. If a new row is added to lookup_1 (ID will ALWAYS have a unique ID), the search will add it to the lookup_2.
The problem is when row is removed from lookup_1, the above search will NOT remove that "old" row from lookup_2 that is NOT existing in inlookup_1 anymore. Example of the problem:

1. lookup_1 has been updated with 10 rows, above search runs and updates lookup_2 which now has 10 rows also.
2. When, a row is removed from lookup_1, now having 9 rows. Search will run and update lookup_2, which will have all 9 "new" rows from lookup_1, but +1 OLD row from previous lookup_1 version.

Search is only appending new rows from lookup_1 to lookup_2, but not removing from lookup_2 if removed from lookup_1.

Hope that explains it better 😕