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?

nickcardenas
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!

1 Solution

pkeenan87
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

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

ddrillic
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

nickcardenas
Path Finder

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

0 Karma

pkeenan87
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

nickcardenas
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

woodcock
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

nickcardenas
Path Finder

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

0 Karma

pkeenan87
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

starcher
SplunkTrust
SplunkTrust

dont add domain_match. Instead

 | lookup mytable domains as queried_domain OUTPUTNEW domains as domain_match
Get Updates on the Splunk Community!

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...