topic How to add a lookup table value to matching search results? in Splunk Search

How to add a lookup table value to matching search results?

techusky — Mon, 04 Apr 2016 17:24:06 GMT

I'm not sure whether or not this is a unique problem, but I'm hoping someone can help even if I'm overlooking an obvious solution :-).

I have a lookup table that is a domain whitelist that we allow through our proxies. For example, let's pretend a portion of this lookup table is like this (keeping in mind that some of the whitelisted domains might be sub-domains):

uri_host
--------
google.com
amazon.com
yahoo.com
answers.splunk.com
.
.
.

What I'm trying to figure out is if there is a way to not only use this lookup table to search across the proxy logs, but also add a field to each resulting event called, say, "match_string" that contains the value from the lookup table that caused the event to match.

For example, if in the proxy logs there are events of people browsing to "maps.google.com" and "images.google.com", those would match my whitelist due to "google.com" being there, but I want to somehow tie that back to the lookup table so that I know it shows up in the results because it matched against "google.com". The results of this might look like:

uri_host              match_string
--------              ------------
maps.google.com       google.com
images.google.com     google.com
mail.yahoo.com        yahoo.com
answers.splunk.com    answers.splunk.com

Hopefully that explains what I'm trying to do well enough, and thank you in advance to anyone who can help!

Re: How to add a lookup table value to matching search results?

somesoni2 — Mon, 04 Apr 2016 19:02:05 GMT

This is a good use-case for Wildcard lookup. See this similar answer for more details

https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html

Basically, have your lookup table as this (say domainlookup.csv)

uri_host, match_string   
*google.com,google.com
*amazon.com,amazon.com
*yahoo.com,yahoo.com
*answers.splunk.com,answers.splunk.com

have your transforms.conf with this

 [domainlookup]
 filename = domainlookup.csv
 match_type = WILDCARD(uri_host)

Now you can add a lookup command to your search OR setup automatic lookup (to add the field match_string automatically to each events of yoursourcetype)

props.conf

[yoursourcetype]
..other settings...
LOOKUP-domain= domainlookup uri_host OUTPUT match_string

Re: How to add a lookup table value to matching search results?

techusky — Mon, 04 Apr 2016 20:50:36 GMT

I think I have things set up as you have suggested, but I'm running into an issue where nothing is actually outputting for the match_string field. I have the config added to the transforms.conf file (but did not add anything into props.conf since I'm not doing an automatic lookup).

Then I'm running the following search, but match_string is blank:

index=proxy_logs | lookup domainlookup uri_host OUTPUT match_string | table uri_host, match_string

Re: How to add a lookup table value to matching search results?

somesoni2 — Mon, 04 Apr 2016 21:28:21 GMT

I'm able to make it work with same settings in my test machine. Can you try to run following query in your instance and let me know if you see a value for the field match_string?

| gentimes start=-1 | eval uri_host="maps.google.com" | table uri_host | lookup domainlookup uri_host

Re: How to add a lookup table value to matching search results?

jmeyers_splunk — Tue, 05 Apr 2016 01:33:19 GMT

this should work.

is it possible that you don't have a field named exactly "uri_host" in your events?

also, if you post your related props.conf & transforms.conf stanzas along with your lookup file definition & sample we can help with debugging a bit more.

Re: How to add a lookup table value to matching search results?

techusky — Tue, 29 Sep 2020 09:21:33 GMT

WOW! Sometimes it's the smallest, dumbest things that trip you up... I was in the process of typing up the relevant part of the transforms.conf as well as a sample of the lookup csv, when I realized that the lookup table had quotes around the field names, so "match_string" instead of just match_string.

I fixed the lookup table and now everything works as expected. Sheesh. 🙂

Re: How to add a lookup table value to matching search results?

jmeyers_splunk — Tue, 05 Apr 2016 15:17:46 GMT

Awesome. Thank you for posting back your results and what lead to your problem. Those pieces of information will help somebody else in the future.