topic Re: Why is my search to match events from a lookup not returning results with my current lookup definition and configuration? in Splunk Search

Why is my search to match events from a lookup not returning results with my current lookup definition and configuration?

reswob4 — Tue, 04 Nov 2014 18:23:55 GMT

I'm sure this has been answered already, but I'm hoping if I write what I did down, someone can point out what I've missed or provide links to other suggestions.

I want to create a search (which I will turn into a dashboard) that will show Microsoft events only where the user is a match for a list of users in a lookup table. So I followed the documentation for creating a lookup table.
http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Addfieldsfromexternaldatasources
http://docs.splunk.com/Documentation/Splunk/6.2.0/Knowledge/Addfieldsfromexternaldatasources#Step_1_-_Add_a_lookup_stanza_to_transforms.conf (and on through the other steps)

So I have a csv file under $SPLUNK_HOME/etc/apps/search/lookups/users.csv

The format of the file is

user,"full_name"
user1, "Last1, First1"
user2, "Last2, First2"
etc

I have created a file-type lookup definition named activeusers that points to that file.

Under $SPLUNK_HOME/etc/apps/search/local I have the following transforms.conf

[activeusers]
filename = users.csv

And under $SPLUNK_HOME/etc/apps/search/local I have the following entry in props.conf

[WMI:WinEventLog:Security]
LOOKUP-user = activeusers user OUTPUT full_name

I put my name in the file to make sure I would get results for testing, but none of my searches get me any results.

Doing a search for

sourcetype="WMI:WinEventLog:Security" user=myusername

shows multiple hits. But when trying to search against the lookup tables, I constantly get No Results Found.

The search I'm using is:

sourcetype="WMI:WinEventLog:Security" | lookup activeusers user (based on: http://stratumsecurity.com/2012/07/03/splunk-security/)

I also tried:
| inputlookup users.csv | search search sourcetype="WMI:WinEventLog:Security"
(this takes a LONG time to run)

Suggestions? Links to better instructions?

Thanks.

Re: Why is my search to match events from a lookup not returning results with my current lookup definition and configuration?

musskopf — Tue, 04 Nov 2014 21:52:47 GMT

Hello reswob4,

I normally use the GUI interface to define the lookups, but as you're using simple CSV files, you don't even need to configure anything to use it at search time. Here some commands to help you debugging it:

Run the search on the "Search App":
| inputlookup users.csv
If you get the lookup output, great, if you don't... something wrong with your CSV file, maybe permissions, character encoding, etc
Having the lookuptable readable do a bit more:
sourcetype="WMI:WinEventLog:Security" user=myusername | lookup users.csv user
Make sure the result from your search is returning a user value that is present in your users.csv
You should be able to see the event now with all additional fields extracted from the lookup table

Let me know the results from that basic test and we should be able to continue from there if you wish to make this an automatic thing to happen.

Re: Why is my search to match events from a lookup not returning results with my current lookup definition and configuration?

reswob4 — Wed, 05 Nov 2014 14:24:24 GMT

| inputlookup users.csv returned the complete list of the csv

sourcetype="WMI:WinEventLog:Security" user=myusername | lookup users.csv user returned a list of all my events in the time period selected as well as provides the full_name field.

So it appears that the basics work....

Re: Why is my search to match events from a lookup not returning results with my current lookup definition and configuration?

musskopf — Mon, 28 Sep 2020 18:06:16 GMT

That's good... now if you wish to make this lookup an automatic thing based on the sourcetype, just go to Settings -> Lookups and make sure you have the Lookup table files, Lookup definitions and Automatic lookups configured.

The Lookup tables file will basically list the files you have inside the local/lookup folder per app. Just define the correct permissions
The Lookup definitions is where you define the Name of a lookup, based on your requirements, if you just give it a name and select the right csv file you'll be OK
And finally the Automatic lookups you associate the definition you created with a sourcetype/host/source... To configure justgive it a name, select a lookup definition and indicate the field names on the event side and on the lookup table. In you case should be on input user=user output full_name=full_name

Re: Why is my search to match events from a lookup not returning results with my current lookup definition and configuration?

reswob4 — Thu, 06 Nov 2014 14:41:59 GMT

OK, I got it. Thanks to musskopf for putting my on the right path. I got the final search syntax from the Exploring Splunk book by David Carasso in the section called: "Comparing Results to Lookup Values"

sourcetype="WMI:WinEventLog:Security" [inputlookup users | fields user]

produced exactly what I want.

Thanks.

Re: Why is my search to match events from a lookup not returning results with my current lookup definition and configuration?

reswob4 — Thu, 06 Nov 2014 14:44:18 GMT

NOTE: I'm marking this as the accepted answer, but musskopf and the book get the credit.

Re: Why is my search to match events from a lookup not returning results with my current lookup definition and configuration?

iaintealecapite — Thu, 11 Jan 2018 14:25:35 GMT

Beware of control characters

My lookup would simply not match, even though I could see that the value in the file was the same as the value returned by the initial search.

This would return correct results:
|inputlookup details.csv |search id="xyz" |table id,description

This would not return any results:
index=_internal|head 1|eval id="xyz"|lookup details.csv id OUTPUT description|table id,description

It turned out that my lookup file had Windows line-breaks (\r\n or CR LF) rather than Linux line-breaks (\n). Correcting this in the file and re-uploading it solved my problem.

I downloaded the file and noticed that my file used windows style line-break characters (CRLF or \r\n) rather than Linux style linebreaks (LF or \n). I think this caused problems for Splunk because my key column was the first column in the file. Once I converted the line-breaks to Linux style ones, and re-uploaded the file, my searches worked perfectly.

Re: Why is my search to match events from a lookup not returning results with my current lookup definition and configuration?

reswob4 — Thu, 11 Jan 2018 14:44:20 GMT

Cool. Thanks for adding that info.