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 is appreciated.
0 Karma
Get Updates on the Splunk Community!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...