Splunk Search

Search inputlookup assistance

mgoudie
New Member

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

Tags (2)
0 Karma

Ayn
Legend

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
0 Karma

hartfoml
Motivator

|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*)

0 Karma

Ayn
Legend

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.

0 Karma

mgoudie
New Member

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.

0 Karma

Ayn
Legend

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.

0 Karma

mgoudie
New Member

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

0 Karma

Ayn
Legend

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.

0 Karma

mgoudie
New Member

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...