Have seen a lot of Q&A about wildcards in the lookup table; this is the reverse. Here is the scenario.
CVE, Patch CVE-2014-2053, Patch A CVE-2015-1111, Patch B
In the events, a given event may have a
cve field as follows:
So I want the above event to match the
CVE-2014-2053 entry in the lookup table. I want to end up with a table of
hostnames that match entries in the lookup table. Some events may have just one
CVE in the
cve field, others may have multiple as above.
Bonus points if this can be done without messing with transforms.conf, etc. which I do not have access to.
I know I can match an event with:
where like(cve,"%CVE-2014-0253%"). But how to extract the matched item of the multiple items?
Thanks, micahkemp, for putting me onto the mvexpand command. However, it took the makemv delim statement to make it happen as needed. So thanks woodcock for the complete solution. Just fyi, I wanted the hostnames for hosts having a CVE in the table. So the actual solution is
1. ...|makeemv delim=";" cve
2. | mvexpand cve
3. | rename cve AS CVE
4. | join CVE [|inputlookup priority_cve]
5. | table hostname cve
When I use lookup instead of join, it does not eliminate the hosts that do not have a match in the table. Perhaps I am not using lookup right, but when I use it per your model, I end up with all events and not just the ones that match.
When you use
lookup to find matching
Patch values for existing
cve values, the
alone will not filter for matches. But the
| search Patch="*" should work to retain only events that have been enriched with the field
Patch. If that doesn't work, try
| where isnotnull(Patch). Either should do the trick.
Yeah, I wasn't doing anything with Patch, that was just for context, sorry for the confusion. I just want to match events to the lookup table, and show the matched events without regard to the Patch field. So imagine the lookup table without the Patch field, and I want events that have a CVE (which could be in the middle of the multivalue cve field) that is in the lookup table. The join works, but if something works better, cool. Using lookup as specified does not do it.
Aha, well there is still a good way to make
lookup work for you. The general intention of the
lookup command is to say, "Do I have a row with the value 'abc' for the field 'xyz'? If so, return the value of field 'def' in that row." Much like you'd look up a word in the dictionary and return the value of field "definition" for that word.
In your case, you just want to know, "Is the value '___' for the field 'cve' present in the lookup table? One way to do that is to still return the value from another field (such as
Patch) if you look up a field and find it in the lookup table. Then you just filter with
| search Patch=* or
| where isnotnull(Patch). If you don't actually want to use the Patch field, no big deal, just discard it by appending
| fields - Patch. Doing this is still less expensive (and less prone to error) than using
join. It's a pattern for using the lookup table to tell you that the value you're looking up is present, even if you don't actually care about the value of the field you return - it's just a flag, effectively.
Yep, that works nicely. Thanks!
| makemv delim=";" cve
| mvexpand cve
| lookup PriorityCVE_test CVE AS cve
| where isnotnull(Patch)
| fields - Patch
| table hostname cve
If your search results are small, you can use
mvexpand. With larger result sets this can be a memory hog, so proceed with caution. Something like
<base search> | table hostname cve | mvexpand cve | lookup priority_cve cve| table hostname cve patch | stats values(patch) AS patch BY hostname.