Splunk Search

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
Communicator

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.

Tags (2)
0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

sundareshr
Legend

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

View solution in original post

sushmitha_mj
Communicator

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 ?

0 Karma

sundareshr
Legend

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

0 Karma

sushmitha_mj
Communicator

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.

0 Karma

sundareshr
Legend

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
Communicator

Awesome.. thanks

0 Karma

sundareshr
Legend

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

0 Karma

sundareshr
Legend

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

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

0 Karma

sushmitha_mj
Communicator

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?

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!