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):
| inputlookup lookup_table_1.csv
| append [| inputlookup lookup_table_2.csv ] | dedup Id | outputlookup lookup_table_2.csv
However, this removes values from manual fields (d, e).
lookup_table_1 - (dynamic)
| field A | field B | field C |
| dynamic_value | dynamic_value | dynamic_value |
lookup_table_2 - (dynamic+2x manual fields(DF))
| field A | field B | field C | field D | field E |
| dynamic_value | dynamic_value | dynamic_value | static_value | static_value |
Is there a way to have dynamic lookup that would update periodically, but preserving 2 manual columns that I could update manually without compromising them each time lookup updates itself?
Thanks in advance!
Regards
Thanks @ITWhisperer , I added all dynamic fields to the lookup command:
| inputlookup lookup_table_1.csv
| lookup lookup_table_2.csv field_a, field_b, field_c
| append [| inputlookup lookup_table_2.csv ] | dedup Id | outputlookup lookup_table_2.csv
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 🙂
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?
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 😕
Would this work?
| inputlookup lookup_table_1.csv
| lookup lookup_table_2.csv
| append [| inputlookup lookup_table_2.csv ] | dedup Id | outputlookup lookup_table_2.csv
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