Splunk Search

Search to get info from lookup file if event field contains data from two field in lookup file?

Abhineet
Loves-to-Learn Everything

Want to create search to get info from lookup file if event field contains data from two field in lookup file.

log event have field "machineUserName" having value "employeeNumber" or "Email-ID" want to do lookup from "workdayData.csv" having two separate field for "employeeNumber" and "Email-ID" want to create lookup query  which will check "machineUserName" field from log event having either "employeeNumber" or "Email-ID" as value will check respective field in lookup and provide other information in lookup table.

Log Event Field

Abhineet_0-1663835259712.png

Lookup-table: WorkdayData.csv

Sample Data

HEADER:empId,empNum,name,email,country,loc,locDesc,OCGRP,OCSGRP,deptName,jobTitle,empStatus,bu,l1MgrEmail
Sample-Data: X0134567,AMAT-0134567,"Jose numo --CNTR","Jose_numo@contractor.amat.com","United States of America",CASCL,"Santa Clara,CA",AGS,OCE,"NACDC NAmer Entity","Logistics Operations - Supplie",Active,"AGS GPS&T, Operations & Central Engineering","Carmy_Hyden@amat.com"

 

Labels (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Abhineet,

the solution is using two times the lookup command:

<your_search>
| lookup workdayData.csv employeeNumber AS machineUserName OUTPUTS <lookup_columns>
| lookup workdayData.csv Email-ID AS machineUserName OUTPUTS <lookup_columns>
| ...

Only one addition information: don't use "-" in field names, it's better "_" becausae it could be interpretated as the subtraction operator.

Ciao.

Giuseppe

0 Karma

Abhineet
Loves-to-Learn Everything

two lookup statement on same lookup file, 

2nd lookup statement overriding first lookup statement output and making it null.

Abhineet_0-1663837350898.png

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Abhineet,

please use OUTPUTNEW instead OUTPUT

<your_search>
| lookup workdayData.csv employeeNumber AS machineUserName OUTPUTNEW <lookup_columns>
| lookup workdayData.csv Email-ID AS machineUserName OUTPUTNEW <lookup_columns>
| ...

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...