topic Re: Query to get info from lookup file if event field contains data from two field in lookup file. in Splunk Search

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

Abhineet — Thu, 22 Sep 2022 15:09:35 GMT

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

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"

Re: Query to get info from lookup file if event field contains data from two field in lookup file.

gcusello — Thu, 22 Sep 2022 08:47:13 GMT

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

Re: Query to get info from lookup file if event field contains data from two field in lookup file.

Abhineet — Thu, 22 Sep 2022 09:02:48 GMT

two lookup statement on same lookup file,

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

Re: Query to get info from lookup file if event field contains data from two field in lookup file.

gcusello — Thu, 22 Sep 2022 10:01:05 GMT

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