topic Lookup in props using combined columns in Getting Data In

Lookup in props using combined columns

koshyk — Fri, 07 Jul 2017 14:38:54 GMT

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>

Re: Lookup in props using combined columns

somesoni2 — Fri, 07 Jul 2017 14:58:49 GMT

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

Re: Lookup in props using combined columns

koshyk — Fri, 07 Jul 2017 15:30:56 GMT

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.