All,
I have this search:
index=main sourcetype=app-gmr eventtype=start_job OR eventtype=end_job
| table _time eventtype jobId JobExecID
| join jobId [ search index=main sourcetype=app-gmr INFO job read was already scheduled
| rex field=_raw "]: {} (?.*) with id (?\d+) "
| stats count by jobname jobId | fields - count ]
It generates the following result:
I need help to format this output to have one line per JobExecId with _time in 2 columns star and end in the same line
Something like:
Jobname jobid JobExecID start end
Thank you,
Gerson
Try this:
|stats earliest(_time) as start latest(_time) as end by JobExecID
Try this:
|stats earliest(_time) as start latest(_time) as end by JobExecID
ehudb,
It worked fine:
index=main sourcetype=app-gmr eventtype=start_job OR eventtype=end_job
| table _time ssnservice eventtype jobId JobExecID
| join jobId [ search index=main sourcetype=app-gmr INFO job read was already scheduled
| rex field=_raw "\]: {} (?.*) with id (?\d+) " ] | **stats earliest(_time) as start latest(_time) as end** by JobExecID
But how can I format the _time to "readable" value?
I tried:
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+) " ]
| stats earliest(strftime(_time,"%F %H:%M:%S:%3N")) as start latest(strftime(_time,"%F %H:%M:%S:%3N")) as end by JobExecID
But I got:
Formatting the time to human readable:
|convert ctime(start) |convert ctime(end)
Overall:
index=main sourcetype=app-gmr eventtype=start_job OR eventtype=end_job
| table _time ssnservice eventtype jobId JobExecID
| join jobId [ search index=main sourcetype=app-gmr INFO job read was already scheduled
| rex field=_raw "]: {} (?.) with id (?\d+) " ] | **stats earliest(_time) as start latest(_time) as end* by JobExecID
|convert ctime(start) |convert ctime(end)
That worked, thank you very much:
index=main sourcetype=app-gmr eventtype=start_job OR eventtype=end_job
| table _time ssnservice eventtype jobId JobExecID
| join jobId [ search index=main sourcetype=app-gmr INFO job read was already scheduled
| rex field=_raw "]: {} (?.*) with id (?\d+) " ]
| stats earliest(_time) as start latest(_time) as end by JobExecID
|convert ctime(start) |convert ctime(end)
Sorry to take advantage of you, I really appreciate your help. The output will have (obviously) the same time for start and end when there is no start_job or end_job records in the output, for example:
2258760 02/09/2017 03:02:30.352 02/09/2017 03:02:30.352
2258779 02/09/2017 02:59:30.369 02/09/2017 02:59:30.369
...
2261282 02/09/2017 14:00:00.110 02/09/2017 14:00:00.110
2261296 02/09/2017 14:05:00.070 02/09/2017 14:05:00.070
Is there any way I could have NOT_STARTED or NOT_ENDED if there is no start_job or end_job records.
This is way to advance to me and I am learning a lot.
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)
It worked, Thank you very much I really appreciate it.
Excellent!
If that worked you can mark the answer as successful.