Splunk Enterprise

Merging fields from two indexes with common field

bud4
Engager

I have two indexes having status of Batch jobs that run in our system daily. 

Source 1:  Contains JobName, StartTime, EndTime, Status. The job status can be - Active, Completed, Failed.
The source or the log name itself is the jobname here. A new event will be created each time the Status changes in the same source. This source contains the up to date information for all jobs except those that are bypassed.

Source 2: This is a DB source containing these fields - JobName, BypassFlag, AvgRunTime. This source contains AvgRunTime of all jobs, BypassFlag which tells if a job was bypassed today. 

Need: I am trying to get each JobName(Source1 and Source2), StartTime(Source1), EndTime(Source1),  JobStatus(Source1), if the job was bypassed today(Source2),AvgRunTime of job.  

Query I tried using Outer join:

I tried using both indexes in same query and also joins but with outer join i am getting results only from the first index even though I am doing an outer join. Please help.

(index="index2" sourcetype=db)
| table JobName,BypassFlag,AvgRunTime
| join type=outer JobName
[search index="index1" host=tnsm123*
| stats latest(JobName), latest(Status), latest(StartTime),latest(EndTime) by source
| table JobName, Status, StartTime, EndTime by source]
| table, StartTime, EndTime, Status, AvgRunTime, BypassFlag

 

Labels (2)
Tags (3)
0 Karma

scelikok
SplunkTrust
SplunkTrust

Hi @bud4,

Please try below sample, there is no need to use join command;

(index=index1 host=tnsm123*) OR (index=index2 sourcetype=db) 
| stats latest(Status) as Status latest(StartTime) as StartTime latest(EndTime) as EndTime latest(AvgRunTime) as AvgRunTime latest(BypassFlag) as BypassFlag by JobName
If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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, ...