Getting Data In

Lookup in props using combined columns

koshyk
Super Champion

While writing props/transforms for an in house TA, i'm stuck with a tricky situation. I'm making use of lookup file to enrich my dataset. But the lookup is a combination of multiple columns in the csv and the dataset

Sample data

firstname=John surname=Travolta city=xyz
firstname=John surname=Grisham city=abc
firstname=John surname=Mcenroe city=tre
firstname=Henry surname=Grisham city=asdf

Sample lookup (mylookup.csv)

firstname,surname,job
John,Travolta,actor
John,Grisham,writer
John,Mcenroe,sports
Henry,Grisham,doctor

if I write a SPL, i would write something like

index=xyz ..| eval first_sur=firstname."_".surname | join first_sur [|inputlookup mylookup.csv| eval first_sur=firstname."_".surname] | table first_sur,city,job

How to write in a transforms/props using lookup? I couldn't find any examples using the eval to combine in a lookup
I'm looking for something of..

#props.conf
LOOKUP-complete_bio = mylookup  <first_sur> OUTPUT <first_sur>
0 Karma
1 Solution

somesoni2
Revered Legend

The lookup command does support matching multiple columns. In SPL you can write like this (no joins required for lookups)

 index=xyz ..| lookup mylookup.csv firstname surname OUTPUT job| table first_sur,city,job

And same thing you'll put in your automatic lookup configurations

props.conf

 LOOKUP-complete_bio = mylookup  firstname surname OUTPUT job

View solution in original post

somesoni2
Revered Legend

The lookup command does support matching multiple columns. In SPL you can write like this (no joins required for lookups)

 index=xyz ..| lookup mylookup.csv firstname surname OUTPUT job| table first_sur,city,job

And same thing you'll put in your automatic lookup configurations

props.conf

 LOOKUP-complete_bio = mylookup  firstname surname OUTPUT job

koshyk
Super Champion

Thank you mate. The query was not as simple as the example, but I made it work.
The greatest sentence from Splunk helped me as I had to do few EVAL before lookups

======

Splunk processes lookups after it processes field extractions, field aliases, and calculated fields (EVAL-* statements). This means that you can use extracted fields, aliased fields, and calculated fields to specify lookups. But you can't use fields discovered by lookups in the configurations of extracted fields, aliased fields, or calculated fields.

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...