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]

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!

[nickcardenas]

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]

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

[nickcardenas]

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

[pkeenan87 · Accepted solution]

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]

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!

[woodcock]

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.

[nickcardenas]

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

[pkeenan87]

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]

dont add domain_match. Instead

 | lookup mytable domains as queried_domain OUTPUTNEW domains as domain_match