Splunk Search

How to search to find a match in lookup file?

surekhasplunk
Communicator

I have 2 lookup files.
Am getting empnumber from one file and then trying to search for the corresponding email id from another lookup file.
Its working fine for until first non-matching empnumber. Once it's not able to find a match it stops there and is not getting further matches.

|inputlookup file1.csv | search bla bla |fields "Employee Name" "Employee Number" |rename "Employee Number" as EmpNum | map[inputlookup file2.csv |search "Employee ID"=$EmpNum$ |eval email=$Employee Email$]

This is working well for first 9 employees then when the 10th one doesnt find a match it stops there even though there is a match on further lines.

Please help.

0 Karma
1 Solution

elliotproebstel
Champion

I'm going to revise what @sheamus69 proposed and also explain why your map command is failing.

First, a revised version of the search that doesn't use map:

| inputlookup file1.csv  
| search blah 
| fields "Employee Name" "Employee Number"  
| rename "Employee Number" as EmpNum  
| lookup file2.csv "Employee ID" AS EmpNum OUTPUT email
| where isnotnull(email)

If there are other fields in file2.csv that you want to display, you can add them to the end of the line starting with | lookup like this:

| lookup file2.csv "Employee ID" AS EmpNum OUTPUT email, field2, field3

The reason @sheamus69's search is returning everything (and not just the items with matches in file2.csv) is because there was no filter at the end of the search to retain only the events with matches. That's what the | where isnotnull(email) part of my answer solves.

In general, using map should be an option of last resort, because Splunk spins up a whole new search for each mapped subsearch, using an incredible amount of resources. And nearly every time you want to use map, there will be a more efficient way to structure your search that doesn't use it. But if you do use it, you should be aware of the maxsearches attribute. If you don't specify a value for maxsearches, the default is 10 - which is exactly what you were hitting. Your search wasn't stopping because it didn't find a match for the 10th entry; it was stopping because you didn't specify an alternate value for maxsearches.

View solution in original post

elliotproebstel
Champion

I'm going to revise what @sheamus69 proposed and also explain why your map command is failing.

First, a revised version of the search that doesn't use map:

| inputlookup file1.csv  
| search blah 
| fields "Employee Name" "Employee Number"  
| rename "Employee Number" as EmpNum  
| lookup file2.csv "Employee ID" AS EmpNum OUTPUT email
| where isnotnull(email)

If there are other fields in file2.csv that you want to display, you can add them to the end of the line starting with | lookup like this:

| lookup file2.csv "Employee ID" AS EmpNum OUTPUT email, field2, field3

The reason @sheamus69's search is returning everything (and not just the items with matches in file2.csv) is because there was no filter at the end of the search to retain only the events with matches. That's what the | where isnotnull(email) part of my answer solves.

In general, using map should be an option of last resort, because Splunk spins up a whole new search for each mapped subsearch, using an incredible amount of resources. And nearly every time you want to use map, there will be a more efficient way to structure your search that doesn't use it. But if you do use it, you should be aware of the maxsearches attribute. If you don't specify a value for maxsearches, the default is 10 - which is exactly what you were hitting. Your search wasn't stopping because it didn't find a match for the 10th entry; it was stopping because you didn't specify an alternate value for maxsearches.

surekhasplunk
Communicator

Perfect Thanks @elliotpreoebstel

0 Karma

sheamus69
Communicator

My answer was typed quite quickly, so I forgot about bunging in a filter.

This is a much better way of explaining it.

0 Karma

sheamus69
Communicator

That search looks a little convoluted for what you're trying to do. Instead of using a map search, just try using a second lookup:

|inputlookup file1.csv  
|search blah 
|fields "Employee Name" "Employee Number"  
|rename "Employee Number" as EmpNum  
|lookup file2.csv EmpNum OUTPUT email

This should work more efficiently.

surekhasplunk
Communicator

Hi @sheamus69

Its giving me all the results from 1st lookup file not getting me the match emails
And more over the column name is "Employee ID" in second lookup file and i have to search for the empnum got in 1 file to get the match first

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...