topic Lookup table to return column value in Splunk Search

Lookup table to return column value

AbubakarShahid — Tue, 29 Sep 2020 16:24:36 GMT

I am having issues with displaying data based off the results from the lookup table.
I am using this search below, which works perfectly fine but the only issue I have is, it does not tell me which IOC caused the event to be generated. I can tell by looking at the raw event or other interesting fields but that is time consuming which beats the purpose of the dashboard that I am building out.

Search:

Index=zyx [|inputlookup URLList.csv | rename BI_URL as query | fields query]
|lookup URLList.csv BI_URL AS url OUTPUTNEW BI_URL IOCs_ADDED_DATE REPORT_TYPE THREAT_TYPE
|lookup URLList.csv BI_URL AS dest_hostname OUTPUTNEW BI_URL IOCs_ADDED_DATE REPORT_TYPE THREAT_TYPE
|lookup URLList.csv BI_URL AS dest OUTPUTNEW BI_URL IOCs_ADDED_DATE REPORT_TYPE THREAT_TYPE
|rename BI_URL as IOC_Match
|table _time IOC_Match IOCs_ADDED_DATE REPORT_TYPE THREAT_TYPE index sourcetype url dest dest_ip src_ip _raw

Events are being generated but these fields are return blank:
IOC_Match IOCs_ADDED_DATE REPORT_TYPE THREAT_TYPE.

Here is scenario:
so let say in the lookup table i have a IOC value, cnn.com and i run the query above.
which will work perfectly fine. it will show there was network traffic going out to money.cnn.com or cnn.com/xyz and it will show that in the dest or url field. How can i make that show the IOC cnn.com? I have the lookup table to display in the IOC_Match column?

Re: Lookup table to return column value

elliotproebstel — Tue, 24 Oct 2017 18:15:14 GMT

I suspect the problem is related to capitalization. This search index=zyx [|inputlookup URLList.csv | rename BI_URL as query | fields query] will generate matches that are case-insensitive. So if your lookup table contains an entry with BI_URL="CNN.COM", that search will return events with CNN.COM or cnn.com or even cNn.cOm.

However, the lookup functionality is NOT case-insensitive. So in the above possibilities, the only events that will generate hits from the | lookup URLList.csv... portions of the search are those containing CNN.COM, not any of the ones containing only cnn.com, for example.

Your best bet is to convert the entire lookup table to lowercase and then start your search with this:
index=zyx [|inputlookup URLList.csv | rename BI_URL as query | fields query] | foreach url dest_hostname dest [eval <<FIELD>>=lower(<<FIELD>>)] before applying all the lookups.

Re: Lookup table to return column value

AbubakarShahid — Tue, 24 Oct 2017 19:05:48 GMT

No this does not resolve the issue either. The IOC_Match column is still empty.

Re: Lookup table to return column value

elliotproebstel — Tue, 24 Oct 2017 19:35:40 GMT

Oh, I'm sorry - I misread your final paragraph. In order to take an event with url="cnn.com/xyz" and make it match on a lookup table containing host="cnn.com", you would need a wildcard lookup. Here's some good guidance on that:
https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html

Caveat here is that converting your existing lookup table to a wildcard lookup may compel you to either:
1. Add a non-wildcarded column that you use in the subsearch to avoid searching on wildcarded fields; OR
2. Apply some string-replacement functions to remove the wildcards from the host field before populating it out into the base search.