Matching a substring with a lookup field

[nabeel652]

Hello wonderful Splunkers

I know we can have a WILDCARD match in a lookup where we can match a key to a wildcard in the lookup. However, my requirement is other way round.

Can we use a wildcard in the matching field and compare to a lookup field? Something like this

| eval email = "*abc1@gmail.com*"
| lookup user_emails all_emails as email output user_address

for this particular user abc1 we have

all_emails = "abc1@yahoo.com,abc1@hotmail.com,abc1@gmail.com"

I know we can modify the lookup to split the all_emails field and then mvexpand to create another lookup to match. However, I'd be interested to know if there is a way without creating another lookup or mvexpanding the current. Just a plain match on all_emails field to find if the 

[yuanliu]

My question to you is: Why do you want to make the match in this specific manner?  What produces the value of field email in that search?  Obviously in the real use case you do not populate email by evaluating a fixed string into it.  If it comes from a search result, why would it have asterisks like that?  This is to say, how does real data look like?  Like several have already indicated, Splunk does not use the kind of expression.

However, this does not mean that you cannot achieve what you want to do, or you have to make another lookup.  It is so much better to illustrate your use case than asking volunteers to diagnose  confusing SPL that obviously does not work as desired.

Here are four golden rules; nay, call them the four commandments if you want to ask answerable questions:

• Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search (SPL that volunteers here do not have to look at).
• Illustrate the desired output from illustrated data.
• Explain the logic between illustrated data and desired output without SPL.
• If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.

As speculated above, I doubt if your "natural" search will return a field with those terminating asterisks in value.  Instead, you probably have events that have email field with values such as abc1@yahoo.com , OR abc1@hotmail.com, OR abc1@gmail.com .  But not something even close to "*abc1@gmail.com*".

If this is the case, why construct your lookup with "abc1@yahoo.com,abc1@hotmail.com,abc1@gmail.com" in the first place?  There should be no need for such a lookup.  What's wrong with producing only one lookup, but with something like

user_email	user_address
abc1@yahoo.com	abc1 address
abc1@hotmail.com	abc1 address
abc1@gmail.com	abc1 address

If anything, exact matches are less expensive, too.

[nabeel652]

Hello @yuanliu 

Thanks for the reply. Well, I know there are other solutions to this problems and the one you mentioned was already on my list. However, I wanted to to know just in case we have a way to match a substring to a larger block of text in a lookup. 
For example an organisation has 100,000 employees that can register with an email and phone number. Phone number is unique but they can add multiple emails. So if on average if a user adds 3 different emails the size of the lookup with the proposed solution will become 300,000 which is 3 times. However, if we have a way to match from a list of comma separated emails to find out what phone number that employee has, would be great. 
What I have figured out there is currently no such feature in Splunk and might be a good candidate for enhancements. 

[PickleRick]

With this size of your lookup you should use kvstore collection, not csv-backed lookup anyway. And kvstore lookups scale a bit differently.

[PickleRick]

To be fully honest, I'm not sure what you mean.

What are your actual contents of the lookup? Is the line you presented a single entry in your lookup? Or is it a whole lookup?

How is it supposed to match?

[PrewinThomas]

@nabeel652 

Unfortunately it doesnt support. Splunk treats the value you pass into the lookup as a literal string, not a pattern/wildcard.

Its best to submit as feature request at #https://ideas.splunk.com/

Regards,
Prewin
If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[kml_uvce]

I do not think you can do this using lookup but you can achieve  this like 

| eval email="abc1@gmail.com"
| inputlookup user_emails
| where like(all_emails, "%" . email . "%")
| table all_emails user_address