How to search a lookup based on partial match of field values of a base search in splunk

Question

I am trying to combine 2 searches where the outer search passes a value to the inner search and then appends the results. Let me explain:

As of right now, I am searching a set of logs that happens to include people's names and their request type when they call the bank. The one I am focused on is "withdraw inquiry." So we get a list of all people who try to withdraw money based on the following base search.

index=myIndex sourcetype=mySource request_type="withdraw inquiry"
| xmlkv DetailXML
| stats count, values(phone_number), values(activity_summary), values(request_type) values(email) by acct_num name_last name_first
| where count > 1
| sort - count

which results in a table that looks like this:

account number	name_first	name_last	call count	values(phone_number)	value(activity_summary)	values(email)
123456678	smith	john	3	1235550987	withdraw inquiry	[email protected]

This is great but I'd like to append or add another column with info that comes from a lookup table entitled Previously_Compromised_Accounts.csv which looks like this

user	date	intel_source
jsmith001	26DEC2021	fraud

The idea is to search the lookup table, using a partial match "smith," from the name_last field and append the results so that the results look like this

account number	name_first	name_last	call count	values(phone_number)	value(activity_summary)	values(email)	compromisedAccount
123456678	john	smith	3	1235550987	withdraw inquiry	[email protected]	jsmith001 26DEC2021

Answer 1

Enable WILDCARD matching in your lookup definition, then do something like:

<first part of search>
| lookup mylookup user AS name_last OUTPUT date intel_source
<rest of search>

Of course, this will only be potentially helpful if user names incorporate aspects of real names

If you have a John Smith whose username is rockinrutabega1970 or 829911882 or sm792ask38 ... you'll be up the proverbial creek with your attempt to "help" :)