How to search to find a match in lookup file?

[surekhasplunk]

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.

[elliotproebstel · Accepted solution]

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]

Perfect Thanks @elliotpreoebstel

[sheamus69]

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

This is a much better way of explaining it.

[sheamus69]

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]

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