How to search a lookup based on field values of a base search

[Allene139]

Hello everyone, hope you are all well this afternoon.

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

I have made this into a dash board and then subsequently added a drilldown. You click the panel and it then searches a lookup table called Previously_Compromised_Accounts.csv

That search is this:

| inputlookup Previously_Compromised_Accounts.csv
| search name=*$clickValue$*
| table date user 

How can I combine this search? Basically, I would like to add another column "compromisedUser" to the base search. If the base search is ran, then a secondary search would be performed, using the value of "name_last" and search the lookup table, which then appends the results to the base search as "compromisedUser"

if no results come from the search of the Previously_Compromised_Accounts.csv, then that 8th coloumn can remain blank. 

accountNumber	name_first	name_last	call count	values(phone_number)	values(activity_summary)	values(email)	compromisedUser
123456678	Smith	John	3	1235550987	withdraw inquiry	JohnSmith@company.com	26DEC2021 jsmith001

[Allene139]

Hey rich thanks so much, that was very helpful. Unfortunately, the column compromisedUser is blank. I think the problem is that the lookup command might be looking for an exact match.

Notice how the last name is Smith in the previous example, however, his username (AKA user) is jsmith001. So really, I would like to search the user field of Previously_Compromised_Accounts.csv for a string that contains "smith," and it would return jsmith001 and its date of compromise. 

[richgalloway]

You can use the lookup command to check the CSV file and add a column to the table.

index=myIndex sourcetype=mySource request_type="withdraw inquiry"
| xmlkv DetailXML
| stats count, values(phone_number) as phone_number, values(activity_summary) as activity_summary, values(request_type) as request_type values(email) as email by acct_num name_last name_first
| where count > 1
| sort - count
| lookup Previously_Compromised_Accounts.csv name as name_last
| eval compromisedUser = date . " " . user
| table acct_num name_first name_last count phone_number activity_summary email compromisedUser