topic Re: How to get my lookup search to return FieldC from a mylookup.csv on match of FieldA? in Splunk Search

How to get my lookup search to return FieldC from a mylookup.csv on match of FieldA?

AnthonyTibaldi — Tue, 06 Dec 2016 21:10:31 GMT

Hello All,

I have a lookup called mylookup based on mylookup.csv containing 3 fields FieldA, FieldB and FieldC. I am trying to match on FieldA and return FieldC on a match.

If I run

|inputlookup mylookup

I get the lookup contents displayed properly.

If I run

sourcetype="logfile" [inputlookup mylookup | fields FieldA | rename FieldA as Result ]| table Result

I get the interesting events back where the lookup value in FieldA matches the value in the event field Result.

If I run

sourcetype="logfile" | lookup mylookup FieldA as Result OUTPUT FieldB as WhatIWant | search WhatIWant=* | table FieldA, FieldB, Result, WhatIWant

It does not return any results.

What is strange for me is I run a different lookup against another log file and it works fine.
mylookup2 against logfile2.

Any help is much appreciated.

Re: How to get my lookup search to return FieldC from a mylookup.csv on match of FieldA?

lguinn2 — Tue, 06 Dec 2016 22:47:49 GMT

Assume that your logfile contains one field called ID, and that the ID field needs to match FieldA. Then your search could be

sourcetype="logfile" ID=*
| lookup mylookup FieldA as ID OUTPUT FieldB 
| where isnotnull(FieldB)
| table ID FieldB other_fields_from_logfile

Re: How to get my lookup search to return FieldC from a mylookup.csv on match of FieldA?

AnthonyTibaldi — Wed, 07 Dec 2016 13:44:28 GMT

Yes, I would see where that should work but it is not.

Inputlookup seems to find the events but lookup does not.

Also as mentioned what is really strange is another lookup works fine.

mylookup2 against logfile2.

The only difference is mylookup2.csv was added through the UI and mylookup.csv was populated from a search. I have ensured permissions on the lookup table file and definition are the same for mylookup and mylookup2.

Not sure what else to do?

Thanks for the feedback.

Re: How to get my lookup search to return FieldC from a mylookup.csv on match of FieldA?

lguinn2 — Wed, 07 Dec 2016 19:41:09 GMT

Lookups are case-sensitive by default. Could this be the issue?

Re: How to get my lookup search to return FieldC from a mylookup.csv on match of FieldA?

AnthonyTibaldi — Wed, 07 Dec 2016 20:07:44 GMT

I have quadruple checked spelling and case. That is not it.

I'll keep searching. Thanks for the advice.

Re: How to get my lookup search to return FieldC from a mylookup.csv on match of FieldA?

AnthonyTibaldi — Mon, 12 Dec 2016 15:45:31 GMT

OK, I found that my search is correct and does work.

sourcetype="logfile" | lookup mylookup FieldA as Result OUTPUT FieldB as WhatIWant | search WhatIWant=* | table FieldA, FieldB, Result, WhatIWant

This works. I think I was having a problem with my search failing or possibly a delay in my proxy logs. In any case the search above does work. So would the search lguinn offered as a suggestion.

The issue was not with the search it was with the environment. Hope this helps anyone with similar trouble.

Re: How to get my lookup search to return FieldC from a mylookup.csv on match of FieldA?

aaraneta_splunk — Mon, 12 Dec 2016 17:59:50 GMT

Hi @AnthonyTibaldi - Glad to hear that you've found the solution to your question. Please don't forget to click "Accept" below your answer so that other users can easily find it. And be sure to up-vote any helpful comments from lguinn too. Thanks!