topic Re: How do I use a csv to identify search terms and correlate events with metadata? in Splunk Search

How do I use a csv to identify search terms and correlate events with metadata?

Phil219 — Sat, 23 Jan 2016 02:29:43 GMT

I have an index of log data I am trying to search.

I have a seperate csv file containing a list of about 40 search terms with two metadata for each term:

name,benefit,type
banana,5,fruit
ice cream,1,desert
tomato,3,vegetable

I want to then create a dashboard that can peruse through the events with those terms and the corresponding metadata.

Is this feasible? I imagine I could use 40 individual eval functions but I would like to save time by using the csv file.

Re: How do I use a csv to identify search terms and correlate events with metadata?

renjith_nair — Sat, 23 Jan 2016 11:11:26 GMT

lookup is the first option you should try

eg : <your search terms from the index> |lookup <lookupname> name OUTPUT type

Refer here for more info : http://docs.splunk.com/Documentation/Splunk/6.1/SearchReference/lookup

Re: How do I use a csv to identify search terms and correlate events with metadata?

Phil219 — Sun, 24 Jan 2016 00:02:03 GMT

I have an index I am trying to search.

I have a seperate csv file containing a list of about 40 search terms with two metadata for each term:

name,benefit,type
banana,5,fruit
ice cream,1,desert
tomato,3,vegetable

I want to then create a table with just the events with those names and the corresponding metadata.

Is this feasible? I imagine I could use 40 individual eval functions but I would like to save time by using the csv file.

When I attempt to implement the syntax:

host=coolstuff | lookup food name OUTPUT type| table text, type

I was hoping to get a table containing text with the particular food called out along with the corresponding type from the lookup file. Instead, when I ran the search, my search returns ALL the events (not just those with the food name) and then the statistics tab returns (0) - there is no table with just text or type.

Can you help me troubleshoot where I went wrong?

I've tried mixing up the syntax to get the results I want, but am just drawing a complete blank as to how to use this command.

host=coolstuff | input food name (AS text) OUTPUT type | table text, type

Re: How do I use a csv to identify search terms and correlate events with metadata?

renjith_nair — Sun, 24 Jan 2016 03:43:14 GMT

Is food your csv name or lookupname? what fields you are getting out of your index search ?

If you have a field name in your search result, you can use that lookup in your csv file and pick up the respective benefit and type from it.

See here : http://docs.splunk.com/Documentation/Splunk/6.2.0/Knowledge/Addfieldsfromexternaldatasources#CSV_lookup_example

Re: How do I use a csv to identify search terms and correlate events with metadata?

Phil219 — Tue, 26 Jan 2016 20:13:13 GMT

Thank you Renjith,

I bet the answer is pretty simple. I just am having some difficulty here. The pages you referred me to are quite informative. I am grateful for your assistance.

"food.csv" is the csv name as well as the lookup table name I uploaded into

.../manager/search/data/lookup-table-files

and the Lookup definition I created in

.../manager/search/data/transforms/lookups

I then followed the instructions found here:

http://docs.splunk.com/Documentation/Splunk/6.2.0/Knowledge/Usefieldlookupstoaddinformationtoyourevents

to make my lookup automatic.

Unfortunately, the fields I requested do not show up in the side bar. Nor does the field "name" appear there. I was hoping that the names from my csv would search all fields and return the corresponding results from the other columns. The field text is there though.

Re: How do I use a csv to identify search terms and correlate events with metadata?

renjith_nair — Wed, 27 Jan 2016 04:19:11 GMT

Ok let me explain what I have done.

I created lookup file called food.csv with below values

name,benefit,type
banana,5,fruit
ice cream,1,desert
tomato,3,vegetable

Uploaded the look up file using Settings >>Lookups » Lookup table files and set permissions
Created a lookup definition food and selected type as File based and the lookup file as food
Tested my lookup by just executing |inputlookup food in my search window and data is visible
Created dummy event with name filed as "banana" to test and did a lookup against food and all my related fields (benefit and type) are displaying

|stats count|eval name="banana"|lookup food name|table name benefit type

Let me know in which step you are facing issue

Re: How do I use a csv to identify search terms and correlate events with metadata?

Phil219 — Wed, 27 Jan 2016 21:23:33 GMT

Step four. Unfortunatley, my food "names" are hidden in text. Do I need to do a rex command to create fields?

Re: How do I use a csv to identify search terms and correlate events with metadata?

renjith_nair — Thu, 28 Jan 2016 02:50:56 GMT

Yes you have to extract the fields to match against lookup.
If not you have to change the lookup table with wildcards and enable wildcard match.

My lookup table contains now name with wildcard

(name,benefit,type
banana,5,fruit

Added match-type to transforms.conf

 [food]
filename = food
match_type = WILDCARD(name)

Created dummy event with name inside text and is working

| stats count | eval name = "This is my banana" | lookup food name

Its preferred to extract the filed and match against lookup instead of this wildcard for accuracy

Re: How do I use a csv to identify search terms and correlate events with metadata?

Phil219 — Fri, 29 Jan 2016 20:56:20 GMT

Thank you so much for your patient instruction! I just updated my lookup table to look like:

  name,benefit,type
 *banana*,5,fruit

and then I tried:

|stats count | eval name="i want bananas" | lookup food name

and it returned four columns, two of them containing values:

count benefit type name
0 "i want bananas"

So, something is working! I couldn't even get the columns to show up before...

Perhaps it is because I do not know how to change transforms.conf

On this page: http://docs.splunk.com/Documentation/Splunk/6.2.0/Knowledge/Usefieldlookupstoaddinformationtoyourevents

It said that:

Use the Settings > Lookups > Lookup definitions page to define the lookup table or edit existing lookup definitions. You can specify the type of lookup (file-based or external) and whether or not it is time-based. Once you've defined the lookup table, you can invoke the lookup in a search (using the lookup command) or you can configure the lookup to occur automatically. Note: This is equivalent to defining your lookup in transforms.conf.

On the Settings>Lookups>Lookup definitions page, I could not find a way to mimic "match_type=WILDCARD(name)"

Is there a way to do that without editing transforms.conf? If not, how would I edit transforms.conf?

Re: How do I use a csv to identify search terms and correlate events with metadata?

Phil219 — Sat, 30 Jan 2016 00:43:33 GMT

Ok. Thank you. I will try this out and let you know how it goes.

Re: How do I use a csv to identify search terms and correlate events with metadata?

renjith_nair — Sat, 30 Jan 2016 03:00:37 GMT

No problem. Just edit your transforms.conf for the lookup on disk. If you have created this lookup in search app, and not shared (private)

Location : SPLUNK_HOME/etc/users//search/local

If it's shared in app then,

Location : SPLUNK_HOME/etc/apps/search/local/

You will be able to see your food stanza in the transforms.conf . Just add the macth_type parameter under that

Re: How do I use a csv to identify search terms and correlate events with metadata?

Phil219 — Sat, 30 Jan 2016 06:03:35 GMT

NICE!!

It worked!