topic Re: Search inputlookup assistance in Splunk Search

Search inputlookup assistance

mgoudie — Mon, 03 Jun 2013 02:15:39 GMT

Hi All,
I've been trying to build on an existing search I've got working and find myself going around in circles and hoping for some collective experience to get to the answer.

The successful search is
source="file.log" [| inputlookup domain.csv | rename HostAddress as query| fields + query ]

File.log contains the source field I'm using "query" and domain.csv is a lookup I've defined. This pulls out just the records in file.log that match on HostAddress in domain.csv. Perfect.

Where I'm pulling my hair out is that I want to pull a second column out of domain.csv and add that to the search result. Let's call the second column "test-result". I've read lots of articles about Lookup definitions and Automatic lookups, but these dont seem to work when I've using the inputlookup to make a subset of records. For example, my file.log file has many hundreds of thousands of lines, but I only want the six lines that have a match to domain.csv and I want a second column from domain.csv to be returned.

Thanks

Re: Search inputlookup assistance

Ayn — Mon, 03 Jun 2013 07:00:57 GMT

Well what you describe certainly sound like using the lookup file for a completely regular lookup? You got your log source with some field, and a CSV file that you want to match certain field values and output a matching other field value based on that. So, something like

source="file.log" [| inputlookup domain.csv | rename HostAddress as query| fields + query ] | lookup domain.csv somefield OUTPUT testresult

Re: Search inputlookup assistance

mgoudie — Mon, 03 Jun 2013 07:15:25 GMT

Hi Ayn, thanks for replying. I tried this recommendation and from my query above did the following.

source="file.log" [| inputlookup domain.csv | rename HostAddress as query| fields + query ] | lookup domain.csv HostAddress OUTPUT testresult

The query runs OK, but I dont see any output for testresult and I get a message "Assuming implicit lookup table with filename 'domain.csv'."

Just to be clear.

file.log (main log file)
~~~~~
query
[other fields]

domain.csv (lookup file)
~~~~~~~~~
HostAddress (used to lookup on query in file.log)
testresult
[other fields]

Thanks.

Re: Search inputlookup assistance

Ayn — Mon, 03 Jun 2013 07:18:44 GMT

That message is OK. That's just because you're not referencing a lookup that's been defined in transforms.conf anywhere, just the direct filename.

When you say you're looking up HostAddress, is this the actual field name in your event data? Because if it isn't this won't work - Splunk needs to know in which field it should look for the values you want to lookup.

Re: Search inputlookup assistance

mgoudie — Mon, 03 Jun 2013 07:44:05 GMT

Hi Ayn, I just updated the headers in the lookup (CSV) file to match the field names in the LOG file and same result (ie Changed HostAddress to query in the CSV file). It's doing something right though as if I mess up the field name of the OUTPUT statement e.g. OUTPUT nonExistant, the search errors. I'm just not seeing output in the event window or have a selectable field in the field selector.

Thx

Re: Search inputlookup assistance

Ayn — Mon, 03 Jun 2013 07:55:49 GMT

Well lookup will throw an error if you ask it to output a field that doesn't exist in the lookup. Same thing happens if you specify a non-existent input field. I'm still not sure you're getting this completely right though. You mention using "query" as an input field - do you really have a "query" field in your data that you want to match, or are you trying to match the complete raw event data against a lookup? You need to give Splunk a SPECIFIC field to use for lookups.

Re: Search inputlookup assistance

mgoudie — Mon, 03 Jun 2013 07:59:19 GMT

I have a field called query in my log data. It's searchable and all that good stuff, so no problems there from what I can see.

Re: Search inputlookup assistance

Ayn — Mon, 03 Jun 2013 08:03:37 GMT

OK. Well, given a field "query" in the event data and your lookup, and an exactly matching value, lookup WILL output a value for the specified output field, so if that's not working you have a problem somewhere. It's hard for me to say more without specific event samples, configurations and search strings.

Re: Search inputlookup assistance

hartfoml — Mon, 24 Jun 2013 20:49:30 GMT

|fields + query] may not be the right search for you. when the literal search is formed using this term "query" the search looks like this source="file.log" (lookup1) OR (lookup2) OR (lookup3)

you may want to look for something else like this
| rename HostAddress as foo| fields + foo ]
this gives a litteral search like this
source="file.log" (foo=lookup1) OR (foo=lookup2) OR (foo=lookup3)
If you want to add wildcard you can do this
| eval foo="*"+HostAddress+"*" | fields foo]
this will give this literal search
source="file.log" (foo=*lookup1*) OR (foo=*lookup2*) OR (foo=*lookup3*)