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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...