topic Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table? in Splunk Search

Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

claatu — Fri, 01 Dec 2017 21:38:29 GMT

Have seen a lot of Q&A about wildcards in the lookup table; this is the reverse. Here is the scenario.

Lookup table priority_cve:

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:

hostname="alpha" cve="CVE-2014-0251;CVE-2014-0253;CVE-2014-0297"

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?

Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

micahkemp — Fri, 01 Dec 2017 22:19:05 GMT

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.

Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

woodcock — Sat, 02 Dec 2017 21:03:48 GMT

Like this:

... | makemv delim=";" cve
| mvexpand cve
|  lookup priority_cve CVE AS cve
| search Patch="*"
| stats values(cve) BY hostname

Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

claatu — Tue, 05 Dec 2017 16:13:30 GMT

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

Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

woodcock — Tue, 05 Dec 2017 16:32:18 GMT

Use lookup; join does not scale and eventually it will break (invisibly).

Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

claatu — Tue, 05 Dec 2017 19:40:44 GMT

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.

Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

elliotproebstel — Tue, 05 Dec 2017 20:11:25 GMT

When you use lookup to find matching Patch values for existing cve values, the lookup
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.

Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

claatu — Tue, 05 Dec 2017 21:09:25 GMT

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.

Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

elliotproebstel — Wed, 06 Dec 2017 01:32:02 GMT

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.

Re: Can I use a wildcard in the field to create a table of hostnames that match entries in the lookup table?

claatu — Mon, 11 Dec 2017 13:41:52 GMT

Yep, that works nicely. Thanks!