How to search a matching string using lookup?

[equick]

I have a query like this, which prints the number of message matches and an abbreviation:

sourcetype=source1 | rex "...(?<message>\S*)..." | eval message=case(like(message, "%message aaa%), "ma", like(message, "%message bbb%"), "mb", like(message, "%message ccc%", "mc", 1=1, message) | stats count by message

Result:

ma 4
mb 1
mc 18

However as I add more messages to the search it's becoming too long so I'm trying to switch to using a lookup table.

I have created a csv lookup called messages.csv (example below) :

longtext,shorttext
message aaa,ma
message bbb,mb
message ccc,mc

and tried various queries including the below but they all fail so was hoping someone here might be able to give me a hint:

sourcetype=source1 | rex "...(?<message>\S*)..." | search [ | inputlookup messages.csv | fields longtext | rename longtext as message] | lookup messages.csv output shorttext | stats count by shorttext

Thanks in advance,
Ed

[equick]

Thanks for your reply. It got me a bit further but I'm still doing something wrong.
Here is the updated lookup table using wildcards:

 longtext,shorttext
*message aaa*,ma
*message bbb*,mb
*message ccc*,mc

I then ran the following query to display the message and its corresponding shorttext:

sourcetype=source1 | rex "...(?<message>\S*)..." | lookup messages.csv longtext as message OUTPUT shorttext | table message, shorttext

This returns some results with the message field, but not the shorttext lookup:

message,shorttext
this is a long message aaa example,
this is a long message ccc example,
this is a long message aaa example,

[xpac]

Did you change the type of the lookup to a wildcard lookup?

[equick]

Could you give me an example of what you mean by that please? I understood that to mean I needed to put wildcards around the longtext values in messages.csv

[somesoni2]

Basically you've to first create a lookup table file (extension .csv) with those wildcard characters around the message field values (which you did) and then create lookup definition (See below link) with MATCH type as WILDCARD. Please note that if you've using Splunk 6.5 or above, you get the MatchType option in Splunk Web UI. If you're using lower Splunk version than that, you'd need to configure that via configuration files on SH (transforms.conf).
Once all those steps are done, do the| lookup using lookup definition.

http://docs.splunk.com/Documentation/Splunk/7.1.0/Knowledge/Usefieldlookupstoaddinformationtoyoureve...

[equick]

Amazing thank you! I was completely oblivious to lookup definitions. I added that and now the lookup works a treat! Thank you both for your answers, and taking the time to help me. I really appreciate it.

Ed

[xpac · Accepted solution]

Hey,

the easiest way should be a wildcard lookup, you can find a good explanation on that topic in this answer:
https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html

You just need to switch the lookup type to wildcard, and append/prepend an * to your message text. 🙂

Hope that helps - if it does I'd be happy if you would upvote/accept this answer, so others could profit from it. 🙂