topic Re: 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? in 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 — Wed, 06 Mar 2019 20:50:26 GMT

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!

Re: 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?

pkeenan87 — Wed, 06 Mar 2019 21:09:16 GMT

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

Re: 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 — Wed, 06 Mar 2019 21:18:06 GMT

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!

Re: 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?

starcher — Wed, 06 Mar 2019 21:51:14 GMT

dont add domain_match. Instead

 | lookup mytable domains as queried_domain OUTPUTNEW domains as domain_match

Re: 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?

pkeenan87 — Wed, 06 Mar 2019 22:03:52 GMT

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

Re: 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?

woodcock — Thu, 07 Mar 2019 05:18:53 GMT

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.

Re: 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 — Fri, 08 Mar 2019 13:29:49 GMT

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

Re: 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 — Fri, 08 Mar 2019 20:33:30 GMT

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.

Re: 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?

ddrillic — Fri, 08 Mar 2019 21:00:59 GMT

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

Re: 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 — Mon, 11 Mar 2019 12:12:05 GMT

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