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?
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.
No this does not resolve the issue either. The IOC_Match column is still empty.
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.