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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...