topic How to create a lookup matching non-exact words ? in Splunk Search

How to create a lookup matching non-exact words ?

damode — Wed, 10 Jan 2018 00:07:53 GMT

I have the below type of event and I want to add a category field to it using lookups

time    Transaction Business name
6/01/2018   40.22   ABC foods 6697    VALE  TAP AND PAY 0000
8/01/2018   45.22   supermarket suburb TAP and PAY 0000

So, I created the following lookup - test.csv

Business name,Category
ABC foods 6697    VALE  TAP AND PAY 0000,Dine out
DEF utilities,Utilities
TARGET suburb name,Shopping
supermarket suburb TAP and PAY 0000,Groceries

Below is my search query,

index="finance" sourcetype="csv_finance" | lookup test.csv "Business name" OUTPUT Category|  table "Business name" Category

but its not displaying the results.
How can I create a successful lookup that will display the categories along with the business name in the search results ?

Re: How to create a lookup matching non-exact words ?

micahkemp — Wed, 10 Jan 2018 00:44:14 GMT

Does your lookup table have the numbers at the front of the line? It's displayed with two leading numbers, which seems to indicate the 2nd number per line is actually in your file.

Re: How to create a lookup matching non-exact words ?

damode — Wed, 10 Jan 2018 00:48:35 GMT

No, the lookup table doesnt have any numbers. Sorry, I got the formatting wrong while posting this qn. I will update it.

Re: How to create a lookup matching non-exact words ?

micahkemp — Wed, 10 Jan 2018 00:51:59 GMT

It's also unclear from your posted data what the values of each field are in the event. Is the Business Name ABC foods 6697 VALE TAP AND PAY 0000?

Re: How to create a lookup matching non-exact words ?

damode — Wed, 10 Jan 2018 00:53:39 GMT

Yes, thats right.
the data is of bank transactions in csv file format.

Re: How to create a lookup matching non-exact words ?

micahkemp — Wed, 10 Jan 2018 00:55:00 GMT

You can do wildcard matching in your lookup. And to avoid confusion I'd use _ instead of in your field names in the lookup.

transforms.conf:

[test]
filename = test.csv
match_type = WILDCARD(Business_name)

test.csv:

 Business_name,Category
 ABC*,Dine out
 DEF*,Utilities
 TARGET*,Shopping
 supermarket*,Groceries

And when searching:

index="finance" sourcetype="csv_finance" | lookup test Business_name AS "Business name" OUTPUT Category|  table "Business name" Category

Re: How to create a lookup matching non-exact words ?

damode — Wed, 10 Jan 2018 01:56:45 GMT

Thanks @micahkemp!