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