Splunk Search

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

techusky
Explorer

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!

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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

techusky
Explorer

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

jmeyers_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

techusky
Explorer

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

jmeyers_splunk
Splunk Employee
Splunk Employee

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.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...