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.
Index=zyx [|inputlookup URLList.csv | rename BIURL as query | fields query]
|lookup URLList.csv BIURL AS url OUTPUTNEW BIURL IOCsADDEDDATE REPORTTYPE THREATTYPE
|lookup URLList.csv BIURL 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?
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 even
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.
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:
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.