Knowledge Management

Creating Lookup table from another dynamic Lookup table

Splunk1ngSpree
Explorer

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

Labels (1)

Splunk1ngSpree
Explorer

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 🙂 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

Splunk1ngSpree
Explorer

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 😕

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...