Archive

Help need to format output

Explorer

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:

alt text

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

Tags (1)
0 Karma
1 Solution

Contributor

Try this:

|stats earliest(_time) as start latest(_time) as end by JobExecID 

View solution in original post

0 Karma

Contributor

Try this:

|stats earliest(_time) as start latest(_time) as end by JobExecID 

View solution in original post

0 Karma

Explorer

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

alt text

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:

alt text

0 Karma

Contributor

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)

0 Karma

Explorer

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.

0 Karma

Contributor

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)
0 Karma

Explorer

It worked, Thank you very much I really appreciate it.

0 Karma

Contributor

Excellent!
If that worked you can mark the answer as successful.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!