Splunk Search

How do I search from a lookup table and match when part of a string from an events' field matches a value in a lookup table?

Path Finder

Hi all,

I know many questions exist similar to this one but none are useful for my particular use case. Please if somebody could advise me on how to do this, I'd appreciate it!

I have a lookup table(mytable) that looks like this. (note, I can add or remove the asterisk easily - i'm just unsure if it'll be useful or not later on

domains
*domain.com
*someotherdomain.com

I created the following search but I'm not getting expected result, likely because I'm searching for exact matches rather than partial. (a queried_domain value might look like queried_domain=example.domain.com) which explains why I tried using asterisk as a wildcard.

base search 
| lookup mytable domains AS queried_domain OUTPUT domains 
| search domains=* 
| sort -_time 
| table _time request_ip queried_domain

Can anyone please tell me how I'd go about doing this?

Thank you!

0 Karma
1 Solution

Communicator

You will need to create a lookup with the wildcard match type.

transforms.conf

[mytable]
filename = mytable.csv
match_type = WILDCARD(domains)

mytable.csv

domains
*domain.com
*someotherdomain.com

Then use this in your search

base search 
| lookup mytable domains as queried_domain OUTPUTNEW domains as domain_match
| where isnotnull(domain_match)
| sort -_time 
| table _time request_ip queried_domain

View solution in original post

Path Finder

For those who stumble on to this question, this solution ended up working as expected:

base search
    [| inputlookup mytable 
    | rename domains AS queried_domain ] 
| sort -_time 
| table _time request_ip queried_domain

Knowing when to use inputlookup and lookup was the confusion here.

Ultra Champion

Right, it's always good to have a reference to this masterpiece answer by @acharlieh at What is the basic difference between the lookup, inputlook and outputlookup commands

0 Karma

Path Finder

This helped clarify a lot! Thank you for linking that!

0 Karma

Communicator

You will need to create a lookup with the wildcard match type.

transforms.conf

[mytable]
filename = mytable.csv
match_type = WILDCARD(domains)

mytable.csv

domains
*domain.com
*someotherdomain.com

Then use this in your search

base search 
| lookup mytable domains as queried_domain OUTPUTNEW domains as domain_match
| where isnotnull(domain_match)
| sort -_time 
| table _time request_ip queried_domain

View solution in original post

Path Finder

Could you see any other implementation without changing the columns of the lookup? It's generated by a dashboard not owned by me (where a user inputs just a domain and it's added into that lookup).

Also, would you mind explaining how |where isnotnull(domain_match) would return events from the base search that would match when a csv value partially matches a field from those events?

Thank you!

0 Karma

Esteemed Legend

First of all, he is not asking you to change the lookup. Second, you told us that if necessary, changing it would not be a problem: I can add or remove the asterisk easily. What he is telling you to do is to create a Lookup definition that points to the #existing, unmodified#Lookup file and then referencing the Lookup file #through# the Lookup definition which qualifies how the lookup will behave. You can have multiple Lookup definitions with different configurations pointing to the same Lookup file. So if there is already a Lookup definition called mytable, then create a new one called mytable2 and use the new one.

0 Karma

Path Finder

Ohhhh okay, now I'm following.. Thank you for clarifying!

0 Karma

Communicator

True, you dont have to add the domain_match field. You could change the lookup command as @starcher suggested. Just a preference of mine

SplunkTrust
SplunkTrust

dont add domain_match. Instead

 | lookup mytable domains as queried_domain OUTPUTNEW domains as domain_match
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!