Splunk Search

how to join 3 different data sources

Laya123
Communicator

Hi,

I have 3 different sources. I want to merge splunk search data with another data of 2 different csv files using inputlookup.

From all 3 different sources p_name is the common field. But csv files having more number of records (this file is having duplicate project names) than IVW logs. I want to lookup with csv file for latest values.

Example:

from IVW logs, I got output like

p_name; respondents; host
A 20 X
B 25 Y
C 5 Z
D 12 W
E 6 V

From A-N.csv, I have fileds like

p_code; O_Status; O_Server; Contact; SVer DateTime
A; Pass; AB1; abc@f ; 2; 23/01/2016 06:30:20

A; Pass; AB1; abc@f ; 2; 22/02/2016 05:10:20
A; fail; AB1; abc@f ; 2; 13/03/2016 08:30:20
A; Pass; AB1; abc@f ; 2; 05/03/2016 07:20:20
A; Pass; AB1; abc@f ; 2; 06/03/2016 05:30:20
A; Pass; AB1; abc@f ; 2; 11/03/2016 05:30:20
C; fail; NA Not found NA 15/01/2016 05:30:20
C; Pass; AB1; abc@f ; 2; 05/02/2016 05:30:20
C; Pass; AB1; abc@f ; 2; 15/02/2016 05:30:20
C; fail; AB1; abc@f ; 2; 02/03/2016 05:30:20
C; fail; AB1; abc@f ; 2; 13/03/2016 05:30:20
B; Pass; AB1; abc@f ; 2; 12/01/2016 05:30:20
B; Pass; AB1; abc@f ; 2; 11/02/2016 05:30:20
B; Pass; AB1; abc@f ; 2; 10/02/2016 05:30:20
B; Pass; AB1; abc@f ; 2; 25/02/2016 05:30:20
B; Pass; AB1; abc@f ; 2; 02/03/2016 05:30:20
B; Pass; AB1; abc@f ; 2; 07/03/2016 05:30:20
B; pass; AB1; abc@f ; 2; 12/03/2016 05:30:20
H; Pass; AB1; abc@f ; 2; 10/02/2016 05:30:20
H; Pass; AB1; abc@f ; 2; 25/02/2016 05:30:20
H; Pass; AB1; abc@f ; 2; 02/03/2016 05:30:20
H; Pass; AB1; abc@f ; 2; 07/03/2016 05:30:20
M; fail; AB1; abc@f ; 2; 12/03/2016 05:30:20
M; Pass; AB1; abc@f ; 2; 02/03/2016 05:30:20
M; Pass; AB1; abc@f ; 2; 07/03/2016 05:30:20
M; fail; AB1; abc@f ; 2; 12/03/2016 05:30:20
M; Pass; AB1; abc@f ; 2; 10/02/2016 05:30:20

From D1.csv, I have fields like

Project; CCoun; CO1; Contacts

A; ABC ; AB ; AB@g
B; XYZ ; CD ; BC@g

C; LMN ; EF ; CD@g
D; PQR ; GH ; EF@g
E; STU ; YZ ; GH@g

I want my output like

p_name; respondents; host; CCoun; CO1; Contancts; O_status O_Server SVer

A ; 20 ; X; ABC ; AB ; AB@g ; Pass ; AB1 2
B ; 25 ; Y XYZ; CD ; BC@g ; pass ; AB1 2

C ; 5 ; Z LMN ; EF ; CD@g fail ; AB1 2
D ; 12 ; W PQR ; GH; EF@g

E ; 6 ; V STU ; YZ ; GH@g

Below query is giving results but in some places there is data in A-N.csv but not showing in output. Can any one help me to do this.

there is duplicate project names in A-N.csv, but when I am mapping with ivw data it has to show latest values based on the date in A-N.csv

index=ibm sourcetype=ivw host=ABC |eval p_name=upper(p_name) |chart distinct_count(respondentID) as respondents, values(host) as "host", count as Entries, |join max=0 type=outer "p_name" [|inputlookup A-N.csv |rename PCode as p_name|values(O_Server) as O_Server values(O_Status) as O_Status values(SVer) as SVer by p_name] |join max=0 type=outer "p_name" [|inputlookup D1.csv |rename project as p_name|stats values(CCoun) as CCoun values(CO1) as CO1 values(Contacts) as Contacts by p_name ]

Thanks in advance

Tags (2)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Looks like you've csv fiels ingested as lookup table files, so you don't need join at all. It could be as simple as this

index=ibm sourcetype=ivw host=ABC |eval p_name=upper(p_name) |chart distinct_count(respondentID) as respondents, values(host) as "host", count as Entries, 
| lookup A-N.csv PCode as p_name OUTPUT O_Server OStatus Sver 
| lookup D1.csv project as p_name OUTPUT CCount CO1 Contacts

Update

Give this a try

index=ibm sourcetype=ivw host=ABC |eval p_name=upper(p_name) |chart distinct_count(respondentID) as respondents, values(host) as "host", count as Entries by p_name
| lookup D1.csv project as p_name OUTPUT CCount CO1 Contacts
| append [| inputlookup A-N.csv | rename PCode as p_name | sort 0 p_name -DateTime | dedup p_name | table p_name O_Server OStatus Sver ]
| stats values(*) as * by p_name

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Looks like you've csv fiels ingested as lookup table files, so you don't need join at all. It could be as simple as this

index=ibm sourcetype=ivw host=ABC |eval p_name=upper(p_name) |chart distinct_count(respondentID) as respondents, values(host) as "host", count as Entries, 
| lookup A-N.csv PCode as p_name OUTPUT O_Server OStatus Sver 
| lookup D1.csv project as p_name OUTPUT CCount CO1 Contacts

Update

Give this a try

index=ibm sourcetype=ivw host=ABC |eval p_name=upper(p_name) |chart distinct_count(respondentID) as respondents, values(host) as "host", count as Entries by p_name
| lookup D1.csv project as p_name OUTPUT CCount CO1 Contacts
| append [| inputlookup A-N.csv | rename PCode as p_name | sort 0 p_name -DateTime | dedup p_name | table p_name O_Server OStatus Sver ]
| stats values(*) as * by p_name
0 Karma

Laya123
Communicator

Hi,

When I used this query I am getting all values from A-N.csv but I don't want all values from A-N.csv for one project I want to display only latest value from the A-N.csv.

Means from my sample data If Project A is having more than one OStatus but I want only OStatus of latest. If Ostatus for A is having fail on 15/Feb/2016 but on 16/Mar/2016 the project status was Pass I want to display only this pass in my output not all values from A-N.csv

Thanks

0 Karma

Laya123
Communicator

Thank you so much the update one worked for me

Thanks

0 Karma

asimagu
Builder
0 Karma

Laya123
Communicator

Thank you for your immediate response.

I tried this but no luck. Here I am mapping values with csv

Thanks & Regards

0 Karma
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 ...