Splunk Search

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

Phil219
Path Finder

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.

0 Karma
1 Solution

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

Phil219
Path Finder

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

0 Karma

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

Phil219
Path Finder

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/Usefieldlookupstoaddinformationtoyoureve...

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.

0 Karma

renjith_nair
Legend

Ok let me explain what I have done.

  1. I created lookup file called food.csv with below values

    name,benefit,type
    banana,5,fruit
    ice cream,1,desert
    tomato,3,vegetable
    
  2. Uploaded the look up file using Settings >>Lookups » Lookup table files and set permissions

  3. Created a lookup definition food and selected type as File based and the lookup file as food

  4. Tested my lookup by just executing |inputlookup food in my search window and data is visible

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

Phil219
Path Finder

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

0 Karma

renjith_nair
Legend

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.

  1. My lookup table contains now name with wildcard

    (name,benefit,type
    banana,5,fruit

  2. Added match-type to transforms.conf

     [food]
    filename = food
    match_type = WILDCARD(name)
    
  3. 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

---
What goes around comes around. If it helps, hit it with Karma 🙂

Phil219
Path Finder

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

0 Karma

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

Phil219
Path Finder

NICE!!

It worked!

0 Karma

Phil219
Path Finder

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/Usefieldlookupstoaddinformationtoyoureve...

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?

0 Karma
Get Updates on the Splunk Community!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...