How to combine the output of 2 events into a single event ?

[Real_captain]

Hi Team 

Can you please help me to create a statistic table based on the below requirement: 

current output : 

Expected Output: 

Current query : 
Index = xyz source = db 
(TERM(A) OR TERM(B) OR TERM(C) OR TERM(D) ) ("- ENDED" OR "- STARTED" OR "ENDED - ABEND")
| eval Function = case(like(TEXT, "%ENDED - ABEND%"), "ABEND" , like(TEXT, "%ENDED - TIME%"), "ENDED" , like(TEXT, "%STARTED - TIME%"), "STARTED")
| eval DAT = strftime(relative_time(_time, "+0h"), "%d/%m/%Y") , {Function}_TIME=_time
| rename DAT as Date_of_reception
| stats max(Date_of_reception) as Date_of_reception max(STARTED_TIME) as STARTED_TIME max(ENDED_TIME) as ENDED_TIME  by JOBNAME
| eval Application = case ( JOBNAME IN ( "A" ,"B") , "A1" , JOBNAME IN ( "C" , "D" ) , "A2" )
| table Application , JOBNAME, Date_of_reception , STARTED_TIME  , ENDED_TIME 

[Real_captain]

HI @gcusello 

Is it possible to check the STARTED_TIME and ENDED_TIME of particular job of each application instead of earliest/latest time of the application?? 

STARTED_TIME of application A1 = STARTED_TIME of job A 
ENDED_TIME of application A1 = ENDED_TIME of job B

 STARTED_TIME of application A2 = STARTED_TIME of job C 
ENDED_TIME of application A2 = ENDED_TIME of job D



 

[gcusello]

Hi @Real_captain ,

using my search, you take the earliest value and latest value of Application1, using both the jobs, so I suppose that the first value is from Job1 and the latest value is from job2.

Ciao.

Giuseppe

[Real_captain]

Yes. you are right. 
But when the Job A is completed, and JOB B is still not started, then

Application Start Time = Start time of the JOB A and 
Application End Time = End time of the JOB A

If JOB2 is not started, then Application End Time should be NULL as we are checking the end time of Job B. 
Thats why i want to do below : 
Application Start Time of A1 = Start time of the JOB A and 
Application End Time A1 = End time of the JOB B

Application Start Time of A2 = Start time of the JOB C and 
Application End Time A2 = End time of the JOB D

[gcusello]

Hi @Real_captain ,

please try this:

index = xyz source = db (TERM(A) OR TERM(B) OR TERM(C) OR TERM(D) ) ("- ENDED" OR "- STARTED" OR "ENDED - ABEND")
| eval Function = case(like(TEXT, "%ENDED - ABEND%"), "ABEND" , like(TEXT, "%ENDED - TIME%"), "ENDED" , like(TEXT, "%STARTED - TIME%"), "STARTED")
| eval DAT = strftime(relative_time(_time, "+0h"), "%d/%m/%Y") , {Function}_TIME=_time
| rename DAT as Date_of_reception
| eval Application = case ( JOBNAME IN ( "A" ,"B") , "A1" , JOBNAME IN ( "C" , "D" ) , "A2" )
| eval STARTED_TIME=strptime(STARTED_TIME,"%d/%m/%Y"), ENDED_TIME=strptime(ENDED_TIME,"%d/%m/%Y")
| stats 
     earliest(eval(if(job="A",STARTED_TIME,""))) AS STARTED_TIME 
     latest(eval(if(job="B",ENDED_TIME,""))) AS ENDED_TIME  
     BY Application Date_of_reception
| fillnull ENDED_TIME value="Job B not started"
| eval Execution_Time=if(isnull(ENDED_TIME),"Job B not started",ENDED_TIME-STARTED_TIME) 
| eval STARTED_TIME=strftime(STARTED_TIME,"%d/%m/%Y"), ENDED_TIME=if(isnull(ENDED_TIME),"Job B not started",strftime(ENDED_TIME,"%d/%m/%Y")
| table Application Date_of_reception STARTED_TIME ENDED_TIME Execution_Time

Ciao.

Giuseppe

[gcusello]

Hi @Real_captain ,

please try this:

Index = xyz source = db (TERM(A) OR TERM(B) OR TERM(C) OR TERM(D) ) ("- ENDED" OR "- STARTED" OR "ENDED - ABEND")
| eval Function = case(like(TEXT, "%ENDED - ABEND%"), "ABEND" , like(TEXT, "%ENDED - TIME%"), "ENDED" , like(TEXT, "%STARTED - TIME%"), "STARTED")
| eval DAT = strftime(relative_time(_time, "+0h"), "%d/%m/%Y") , {Function}_TIME=_time
| rename DAT as Date_of_reception
| eval Application = case ( JOBNAME IN ( "A" ,"B") , "A1" , JOBNAME IN ( "C" , "D" ) , "A2" )
| eval STARTED_TIME=strptime(STARTED_TIME,"%d/%m/%Y"), ENDED_TIME=strptime(ENDED_TIME,"%d/%m/%Y")
| stats 
     earliest(STARTED_TIME) AS STARTED_TIME 
     latest(ENDED_TIME) AS ENDED_TIME  
     BY Application Date_of_reception
| eval Execution_Time=ENDED_TIME-STARTED_TIME 
| eval STARTED_TIME=strftime(STARTED_TIME,"%d/%m/%Y"), ENDED_TIME=strftime(ENDED_TIME,"%d/%m/%Y")
| table Application Date_of_reception STARTED_TIME ENDED_TIME Execution_Time

Ciao.

Giuseppe