I'm happy to help the learning process 🙂
There are many approaches to your question.
One way of them, we can create new time fields based on the start\end type
|eval starttime=if(eventtype="start_job",_time,null()), endtime=if(eventtype="end_job",_time,null())
|stats earliest(starttime) as start lastest(endtime) as end by JobExecID
|eval job_status=case(isnull(start),"NOT_STARTED",isnull(end),"NOT_ENDED",true(),"Started-Ended")
|convert ctime(start) ctime(end)
Overall query:
index=main sourcetype=app-gmr eventtype=start_job OR eventtype=end_job
| table _time ssnservice eventtype jobId JobExecID
| join jobId [ search index=ssn sourcetype=app-gmr INFO job read was already scheduled
| rex field=_raw "\]: {} (?.*) with id (?\d+) " ]
|eval starttime=if(eventtype="start_job",_time,null()), endtime=if(eventtype="end_job",_time,null())
|stats earliest(starttime) as start lastest(endtime) as end by JobExecID
|eval job_status=case(isnull(start),"NOT_STARTED",isnull(end),"NOT_ENDED",true(),"Started-Ended")
|convert ctime(start) ctime(end)
... View more