Splunk Search

How to correlate 3 different CSV files with different column names?

krishnacasso
Path Finder

I have 3 different source CSV (file1, file2, file3) files.
In file 1, I have field(place) with value NJ and
In file 2, I have a field(city) with value NJ.
NJ is unique value in file 1 and file 2. I need to merge field names to City.
In file 2, I have a field(country) with value USA and
In file 3, I have a field(origin) with value USA.
USA is unique value in file 2 and file 3. I need to merge field names to Country.

I am trying to correlate the fields and join them and create a single table.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try something like this

source=csv1 OR source=csv2 OR source=csv3
| eval city=coalesce(city,place) | eval country=coalesce(country, origin)
| eventstats values(country) as country by city 
| eventstats values(city) as city by country
| stats values(field1) as field1 values(field2) as field2... by city country

View solution in original post

surakshita
New Member

I also same type question. I have 3 excel files generating from the servers as reports,two files are in same format and one file has different columns. I wanted to consolidate 3 files and need to analyze for start time and start date for the job.Can anyone give us the inputs on this?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try this.

| inputlookup file1.csv | lookup file2.csv city AS place | lookup file3.csv origin AS country | eval City=coalesce(place, city), Country=coalesce(country,origin) | ...
---
If this reply helps you, Karma would be appreciated.
0 Karma

krishnacasso
Path Finder

Can we add a condition to the search like to display the chart only for the Manhattan city and USA

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try something like this

source=csv1 OR source=csv2 OR source=csv3
| eval city=coalesce(city,place) | eval country=coalesce(country, origin)
| eventstats values(country) as country by city 
| eventstats values(city) as city by country
| stats values(field1) as field1 values(field2) as field2... by city country
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...