topic Re: How to search a matching string using lookup? in Splunk Search

How to search a matching string using lookup?

equick — Sun, 13 May 2018 17:23:17 GMT

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

Re: How to search a matching string using lookup?

xpac — Sun, 13 May 2018 20:37:31 GMT

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. 🙂

Re: How to search a matching string using lookup?

equick — Mon, 14 May 2018 16:41:41 GMT

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,

Re: How to search a matching string using lookup?

xpac — Mon, 14 May 2018 17:54:37 GMT

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

Re: How to search a matching string using lookup?

equick — Mon, 14 May 2018 18:09:06 GMT

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

Re: How to search a matching string using lookup?

somesoni2 — Mon, 14 May 2018 18:50:27 GMT

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/Usefieldlookupstoaddinformationtoyourevents#Create_a_CSV_lookup_definition

Re: How to search a matching string using lookup?

equick — Tue, 15 May 2018 08:07:29 GMT

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.