How to enhance the search results of events that match a lookup.csv?

[Log_wrangler]

Hi,

I have a query that produces the results I want but now I need to add some extra fields to the events.

I have a lookup csv (e.g. Bad_IOC.csv) with column names "type and value".
Under "type" I may have domains, hashes, IP(s) and under "value" I will have the corresponding , "domain.tld", "file-hashes", "ip_addresses".

Example query

Index = network_data sourcetype=foo [inputlookup Bad_IOC.csv | fields value | rename value as search | format maxresults =1000] | stats values(URL)

Index=network_data contains a field called "URL" which contains strings with domains I want to match against Bad_IOC.csv lookup.

The results of the above query successfully finds matches of URL values with listed domains on Bad_IOC.csv. Lets say "malicious.com" is on the Bad_IOC.csv and when I run the query I get one exact match "malicious.com" and two matches with the pattern "malicious.com" i.e. cdn.malicious.com and san.cdn.malicious.com.edgekey.net...

What I need is to produce table where the following additional information is appended.

URL                                                                  match                         type
cdn.malicious.com                                                  malicious.com                  domain
san.cdn.malicious.com.edgekey.net                                  malicious.com                  domain
malicious.com                                                      malicious.com                  domain

Please advise the best what to create new output from the matches.
The objective is primarily to tag the matches with the ioc (in this case domain "malicious.com") and the type (which is domain)..

Thank you

[nickhills · Accepted solution]

Use the 'lookup' as a 'lookup'!

index = network_data sourcetype=foo |lookup BAD_IOC match as URL OUTPUT match type| table URL match type

To make this work, I assume your CSV contains two columns - 'match', and 'type' (match is a wildcarded url, eg *malicious.com)
I also assume your source data has a field called URL

Once you have your CSV, you need to create a lookup definition BAD_IOC (in my example) which references the CSV, and set the matchtype to WILDCARD for the 'match' field.

[Log_wrangler]

accepting your answer as it is correct, and what I am proposing is not possible I determined. Thanks

[Log_wrangler]

Thank you for the reply. Your assumptions are correct, except in the Bad_IOC lookup there is no field / column name "match". We want the "match" field values to populate with the IOC field which is called "value" (e.g. in this case type = domain and value = malicious.com). The reason is so when the data is processed by our SOAR platform, there is a match value of "malicious.com" for URL values that match something like "san.cdn.malicious.com.edgekey.net " which can be confusing as it is not simply formated domain.tld...

In other words, we wanted the URL field value to match the IOC domain, and produce the tabled output above.

If I am understanding you correctly, we need to add more columns to the BAD_IOC lookup. We are trying to re-work a query that someone else wrote so maybe I just need to start from scratch.

Thanks

[nickhills]

No, just swap the field name “match” for the field “domain” from my example.

I think you would need to prefix the domain list with * to make it work though.

[Log_wrangler]

Thanks for the suggestion but its not working, we have a special situation here.

For example here is a sample output with a domain ioc from BAD_IOC lookup...

|inputlookup BAD_IOC

The results are

type        value
domain  malicious.com

[Log_wrangler]

So here is where we were prior to asking the community...

index=net_data sourcetype=foo
[inputlookup BAD_IOC | fields value | rename value as search | format maxresults=1000]
|rename URL as value
|lookup BAD_IOC value outputnew value AS match, type 
|table value,match,type

Results were sort of on target

value                                      match                    type
malicious.com                          malicious.com        domain
cdn.malicious.com                       
san.cdn.malicious.com.edgekey.net 

we just wanted to add "malicious.com" and "domain" to the other rows that were not exact matches with the ioc domain "malicious.com". Not even sure this is possible, doing this our way. But we are getting matches without wildcarding * the ioc domains.

[Log_wrangler]

in other words we wanted the ioc domain pattern match to dynamically output and populate a field called "match"