Splunk Search

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

aarontmartin165
Explorer

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
Tags (2)
0 Karma

xpac
SplunkTrust
SplunkTrust

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. 😉

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

splunking1t
New Member

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 🙂

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

macadminrohit
Contributor

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...