topic Lookup stating that it cannot find all specified lookup fields in the lookup table. in Splunk Search

Lookup stating that it cannot find all specified lookup fields in the lookup table.

aarontmartin165 — Mon, 30 Apr 2018 19:23:32 GMT

I am attempting to find first time logons to new servers. I am trying to use a lookup to list a EventID Description rather than just the code. I keep getting an error stating "Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table"
Any Idea what I have wrong?

index=* sourcetype="*WinEventLog:Security" 
|lookup windows_event_lookup.csv EventCode AS EventID OUTPUT Event_Desc
| search (4624 OR 4647 OR 4648 OR 551 OR 552 OR 540 OR 528 OR 4768 OR 4769 OR 4770 OR 4771 OR 4768 OR 4774 OR 4776 OR 4778 OR 4779 OR 672 OR 673 OR 674 OR 675 OR 678 OR 680 OR 682 OR 683)  (EventCode=4624 OR EventCode=4647 OR EventCode=4648 OR EventCode=551 OR EventCode=552 OR EventCode=540 OR EventCode=528 OR EventCode=4768 OR EventCode=4769 OR EventCode=4770 OR EventCode=4771 OR EventCode=4768 OR EventCode=4774 OR EventCode=4776 OR EventCode=4778 OR EventCode=4779 OR EventCode=672 OR EventCode=673 OR EventCode=674 OR EventCode=675 OR EventCode=678 OR EventCode=680 OR EventCode=682 OR EventCode=683)
| table user Event_Desc

Re: Lookup stating that it cannot find all specified lookup fields in the lookup table.

somesoni2 — Tue, 29 Sep 2020 19:19:27 GMT

The lookup table windows_event_lookup.csv contains the field EventCode and Event_Desc??

Re: Lookup stating that it cannot find all specified lookup fields in the lookup table.

macadminrohit — Mon, 30 Apr 2018 21:24:06 GMT

And shouldnt he be doing | search EventID= rather than EventCode=

Re: Lookup stating that it cannot find all specified lookup fields in the lookup table.

somesoni2 — Mon, 30 Apr 2018 21:30:13 GMT

yes if his lookup command is correct.. it seems like the order of fields in input part of lookup (after lookup name and before OUTPUT) is reversed, hence Splunk can't find the field EventCode (which is available in indexed data) in the lookup table. Try reversing it.

Re: Lookup stating that it cannot find all specified lookup fields in the lookup table.

DalJeanis — Mon, 30 Apr 2018 21:44:38 GMT

Try

 | lookup windows_event_lookup.csv EventID AS EventCode OUTPUT Event_Desc

I always have to look up the syntax for that one. This assumes that EventCode is the name in the event, and EventID is the field name in the lookup table.

 | lookup <lookup-table-name> <lookup-field1> AS <event-field1>,

from https://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Lookup

Re: Lookup stating that it cannot find all specified lookup fields in the lookup table.

xpac — Mon, 30 Apr 2018 21:52:55 GMT

Your search should most likely look like this:

 index=* sourcetype="*WinEventLog:Security" (4624 OR 4647 OR 4648 OR 551 OR 552 OR 540 OR 528 OR 4768 OR 4769 OR 4770 OR 4771 OR 4768 OR 4774 OR 4776 OR 4778 OR 4779 OR 672 OR 673 OR 674 OR 675 OR 678 OR 680 OR 682 OR 683)  (EventCode=4624 OR EventCode=4647 OR EventCode=4648 OR EventCode=551 OR EventCode=552 OR EventCode=540 OR EventCode=528 OR EventCode=4768 OR EventCode=4769 OR EventCode=4770 OR EventCode=4771 OR EventCode=4768 OR EventCode=4774 OR EventCode=4776 OR EventCode=4778 OR EventCode=4779 OR EventCode=672 OR EventCode=673 OR EventCode=674 OR EventCode=675 OR EventCode=678 OR EventCode=680 OR EventCode=682 OR EventCode=683)
 | lookup windows_event_lookup.csv EventID AS EventCode OUTPUT Event_Desc
 | table user Event_Desc

Putting the search parameters in the first line will make Splunk fetch only those relevant events from the beginning, and also only do the lookup on those events instead of all events. Twice the performance improvement.

The thing about EventCode/EventID being twisted has already been said by others. 😉

Re: Lookup stating that it cannot find all specified lookup fields in the lookup table.

DalJeanis — Mon, 30 Apr 2018 23:03:14 GMT

Only one set should be present -- either the first set of

(4624 OR 4647... )

or the second set of

 (EventCode=4624 OR EventCode=4647 ... )

If anything in the second set is true, the same item in the first set will be true.

Use the second set, with the name of the field as it is on the events.

The first method could be used if the code was present, but had to be extracted via a regular expression after the events have been brought back. That test will limit the events to those that contain one of the numbers, for example 4624, in any field in the event. (ie address="4624 first street, springfield")

You would then, immediately after the extraction, use a search or where to get rid of any events where the number had been found in the wrong field.

Re: Lookup stating that it cannot find all specified lookup fields in the lookup table.

splunking1t — Tue, 29 Sep 2020 19:52:13 GMT

Hello,
I'm having a little twisted requirement than your's. Hoping if someone can help me with it.
We have different arrival times of multiple files that gets created at a common location.
I've created a csv lookup file xyz.csv which has the following rows in it inside splunk.
file_Name: extension_of_the_File: file_id: file_Starting_Name:
1 a tg a
2 b tg b
3 c tg c
4 d tg d
I want to see all the result and if files a,b is not received by x specific time and c,d is not received by Y specific time. I want to generate alert based on this query which will tell me at this time of the day or hour the file was not received.

My base query looks like this:
| inputlookup xyz.csv | join type=outer file_Starting_Name [ search index=index sourcetype=logs
/busdata/etc/host/logs/*.tg
| rex field=raw "/(?[\w]+).flg" | rename fileName as file_Starting_Name
| stats latest(_time) as time by file_Starting_Name, host]

Thanks 🙂