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 ?
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
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
Thanks @micahkemp!
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.
No, the lookup table doesnt have any numbers. Sorry, I got the formatting wrong while posting this qn. I will update it.
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
?
Yes, thats right.
the data is of bank transactions in csv file format.