Splunk Search
Highlighted

Lookup table to return column value

New Member

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 BIURL as query | fields query]
|lookup URLList.csv BI
URL AS url OUTPUTNEW BIURL IOCsADDEDDATE REPORTTYPE THREATTYPE
|lookup URLList.csv BI
URL AS desthostname OUTPUTNEW BIURL IOCsADDEDDATE REPORTTYPE THREATTYPE
|lookup URLList.csv BIURL AS dest OUTPUTNEW BIURL IOCsADDEDDATE REPORTTYPE THREATTYPE
|rename BIURL as IOCMatch
|table time IOCMatch IOCsADDEDDATE REPORTTYPE THREATTYPE index sourcetype url dest destip srcip _raw

Events are being generated but these fields are return blank:
IOCMatch IOCsADDEDDATE REPORTTYPE 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?

0 Karma
Highlighted

Re: Lookup table to return column value

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.

0 Karma
Highlighted

Re: Lookup table to return column value

New Member

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

0 Karma
Highlighted

Re: Lookup table to return column value

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.

0 Karma