topic Re: Help need to format output in Splunk Search

Help need to format output

GersonGarcia — Tue, 29 Sep 2020 12:46:12 GMT

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

Re: Help need to format output

ehudb — Thu, 09 Feb 2017 21:37:03 GMT

Try this:

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

Re: Help need to format output

GersonGarcia — Thu, 09 Feb 2017 22:19:33 GMT

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:

Re: Help need to format output

ehudb — Tue, 29 Sep 2020 12:49:24 GMT

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)

Re: Help need to format output

GersonGarcia — Tue, 29 Sep 2020 12:46:14 GMT

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.

Re: Help need to format output

ehudb — Fri, 10 Feb 2017 11:58:33 GMT

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)

Re: Help need to format output

GersonGarcia — Fri, 10 Feb 2017 17:09:56 GMT

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

Re: Help need to format output

ehudb — Fri, 10 Feb 2017 17:15:08 GMT

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