Dashboards & Visualizations

How to combine 2 csv with 3 csv to get the correct data

aditsss
Motivator

Hi Everyone,

I have one requirement where I need to combine 3 csv with 3 csv for Environment E1,E2 and E3 . I have made the query like this:

|inputlookup JOB_MDJX_CS_STATS_2.csv|append [ inputlookup JOB_MDJX_CS_STATS_2_E2.csv]|append [ inputlookup JOB_MDJX_CS_STATS_2_E3.csv]|join type=outer JOBFLOW_ID [ inputlookup JOB_MDJX_CS_MASTER.CSV ]
|join type=outer JOBFLOW_ID [ inputlookup JOB_MDJX_CS_MASTER_E2.csv ]
|join type=outer JOBFLOW_ID [ inputlookup JOB_MDJX_CS_MASTER_E3.csv]|where Environment="E3"|eval y=20|eval Run_date1= y."".RUNDATE2|eval Run_Date=strftime(strptime(Run_date1,"%Y%m%d"),"%d/%m/%Y")|eval nowdate=strftime(relative_time(now(), "-7d@d" ), "%d/%m/%Y")|stats sum(JOB_EXEC_TIME) as TotalExecTime by JOBFLOW_ID |eval TotalExecTime=round(TotalExecTime,2)|sort -TotalExecTime limit=10

The issue I am facing is its showing the data only for E3 even when I am putting environment as E1 or E2 despite there is Data in E1 and E2.

Can someone guide me what's wrong in my query.

Thanks in advance

Labels (3)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Is JOBFLOW_ID unique across all three environments i.e. each JOBFLOW_ID only exists in one environment (and therefore at most the two corresponding csv files)?

The where clause is restricting the Environment to E3 - are you saying that you only get E3 data even if this where clause is changed to E2 etc?

0 Karma

aditsss
Motivator
Spoiler
 

@ITWhisperer 

Jobflow id is common in all three environments.

I am getting correct data when I combine like this:

|inputlookup JOB_MDJX_CS_STATS_2.csv|append [ inputlookup JOB_MDJX_CS_STATS_2_E2.csv]|append [ inputlookup JOB_MDJX_CS_STATS_2_E3.csv]|where Environment="E2"|eval y=20|eval Run_date1= y."".RUNDATE2|eval Run_Date=strftime(strptime(Run_date1,"%Y%m%d"),"%d/%m/%Y")|eval nowdate=strftime(relative_time(now(), "-7d@d" ), "%d/%m/%Y")|stats sum(JOB_EXEC_TIME) as TotalExecTime by JOBFLOW_ID |eval TotalExecTime=round(TotalExecTime,2)|sort -TotalExecTime limit=10

But when I combine 3 more csv then data is not correct.

Is there something problem in append:

|inputlookup JOB_MDJX_CS_STATS_2.csv|append [ inputlookup JOB_MDJX_CS_STATS_2_E2.csv]|append [ inputlookup JOB_MDJX_CS_STATS_2_E3.csv]|join type=outer JOBFLOW_ID [ inputlookup JOB_MDJX_CS_MASTER.CSV ]
|join type=outer JOBFLOW_ID [ inputlookup JOB_MDJX_CS_MASTER_E2.csv ]
|join type=outer JOBFLOW_ID [ inputlookup JOB_MDJX_CS_MASTER_E3.csv]|where Environment="E2"|eval y=20|eval Run_date1= y."".RUNDATE2|eval Run_Date=strftime(strptime(Run_date1,"%Y%m%d"),"%d/%m/%Y")|eval nowdate=strftime(relative_time(now(), "-7d@d" ), "%d/%m/%Y")|stats sum(JOB_EXEC_TIME) as TotalExecTime by JOBFLOW_ID |eval TotalExecTime=round(TotalExecTime,2)|sort -TotalExecTime limit=10

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

By unique across environments I mean do all the values of  JOBFLOW_ID in JOB_MDJX_CS_STATS_2.csv only exist in JOB_MDJX_CS_MASTER.csv and  all the values of  JOBFLOW_ID in JOB_MDJX_CS_STATS_2_E2.csv only exist in JOB_MDJX_CS_MASTER_E2.csv and  all the values of  JOBFLOW_ID in JOB_MDJX_CS_STATS_2_E3.csv only exist in JOB_MDJX_CS_MASTER_E3.csv

0 Karma

aditsss
Motivator

@ITWhisperer 

yes its correct:

 

By unique across environments I mean do all the values of  JOBFLOW_ID in JOB_MDJX_CS_STATS_2.csv only exist in JOB_MDJX_CS_MASTER.csv and  all the values of  JOBFLOW_ID in JOB_MDJX_CS_STATS_2_E2.csv only exist in JOB_MDJX_CS_MASTER_E2.csv and  all the values of  JOBFLOW_ID in JOB_MDJX_CS_STATS_2_E3.csv only exist in JOB_MDJX_CS_MASTER_E3.csv

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

.conf25 Global Broadcast: Don’t Miss a Moment

Hello Splunkers, .conf25 is only a click away.  Not able to make it to .conf25 in person? No worries, you can ...

Observe and Secure All Apps with Splunk

 Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

What's New in Splunk Observability - August 2025

What's New We are excited to announce the latest enhancements to Splunk Observability Cloud as well as what is ...