How to edit my search to match fields from indexed data with a field in a CSV file to output another corresponding field?

[sushmitha_mj]

I am trying to match the fields countrycode (An eval field extracted from indexed data) with a field "Code" in a CSV file, get the corresponding "branch_name" from the CSV, and display it along with certain fields that are indexed. Here is my current search:

inputcsv sample_csv.csv [ search index="logs" | eval country_code=substr(server_name,3,3) | table  country_code ] | eval Branch=if(Code,country_code,Branch_name) | table Branch, ...

But it is not working.

[sundareshr · Accepted solution]

Lookup table is your best option, but if you choose not to use lookups, try this (i have never used this)

 index="logs" | eval country_code=substr(server_name,3,3) | append [inputcsv sample_csv.csv |rename Code as country_code ] | stats values(Branch_Name) as Branch_name by country_code

[sushmitha_mj]

Hey @sundareshr
I have been using the above query, it is working, I wanted to add a couple of more fields to the result set like Server_name, type etc. These fields are present in the indexed data . But when I add those fields the result set is giving me all the fields in the csv and not restricting the result set to the time filter. How can I enhance the query ?

[sundareshr]

For using time as a filter, you have to configure your lookup as time-based lookup

https://docs.splunk.com/Documentation/Splunk/6.4.3/Knowledge/Configureatime-boundedlookup

[sushmitha_mj]

It actually works 🙂
I also created a lookup table (Just added the csv files as a lookup table). Just curious, how can I extract the same info using the lookup table?

I checked the doc you sent me, but I did not get it.

[sundareshr]

For lookup table, here are the steps

1) Create lookup table. Set appropriate permissions (app level,maybe)
2) Create lookup definition. Set appropriate permissions (app level,maybe)
3) Use in search, like this

index=logs | eval code=substr(server_name,3,3) | lookup lookupfiledefinitonname Code as code OUTPUT Branch_name | rest of your query

[sushmitha_mj]

Awesome.. thanks

[sundareshr]

Learn something new everyday 🙂 Please close out the question by accepting the answer.

[sundareshr]

Have you considered using Lookups? How frequently does your csv file change?

http://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/Lookup

[sushmitha_mj]

The CSV does not change. I did not consider using a lookup. Is that the only option. Can I not do it with a simple query connecting the two sources?