Community:Search Report: How To Merge Three Lookup Tables By Associated Fields

From Splunk Wiki

Jump to: navigation, search
#
# Merge three lookup tables into one based on associated fields
#

1. Three tables 

"product.csv" contains associated keys;
  - LOCATION_ID for "locations.csv"
  - BRAND_ID    for "brands.csv"

- test_table_products.csv 
------------------------------------------
ID, LOCATION_ID, BRAND_ID, PRODUCT_NAME
1,      2,          1,          Dryer
2,      2,          3,          Washer
3,      1,          3,          Microwave
------------------------------------------

- test_table_brands.csv
------------------------------------------
ID, BRAND
1,  Bosh
2,  Philips
3,  GE
4,  Sumsung
------------------------------------------

- test_table_locations.csv
------------------------------------------
ID, LOCATION_NAME
1,  Kitchen
2,  Bathroom
3,  Living
4,  Bedroom
------------------------------------------


2. Splunk search to join them by test_table_product.csv
   | inputlookup test_table_products.csv  
   | join type=outer LOCATION_ID  [ inputlookup test_table_locations.csv | rename ID AS LOCATION_ID ] 
   | join type=outer BRAND_ID     [ inputlookup test_table_brands.csv    | rename ID AS BRAND_ID    ]
   | table ID PRODUCT_NAME BRAND_ID BRAND LOCATION_ID LOCATION_NAME

------------------------------------------------------------------------
ID    PRODUCT_NAME    BRAND_ID    BRAND    LOCATION_ID    LOCATION_NAME
------------------------------------------------------------------------
1       Dryer           1         Bosh         2            Bathroom
2       Washer          3         GE           2            Bathroom
3       Microwave       3         GE           1            Kitchen

Personal tools
Hot Wiki Topics


About Splunk >
  • Search and navigate IT data from applications, servers and network devices in real-time.
  • Download Splunk