How to extract two values from two same fields in two events and then find the difference in timestamp

[vanakkam777]

Log data example:

{'job_no':'1','begin_build_time':'2019-08-15 11:00:00','event_type':'staging'}
{'job_no':'1','end_build_time':'2019-08-15 11:10:00','event_type':'staging'}
{'job_no':'1','begin_execution_time':'2019-08-15 11:10:01','event_type':'transaction'}
{'job_no':'1','end_execution_time':'2019-08-15 11:20:00','event_type':'transaction'}
{'job_no':'1','begin_artifact_time':'2019-08-15 11:20:01','event_type':'upload'}
{'job_no':'1','end_artifact_time':'2019-08-15 11:30:00','event_type':'upload'}

I need data table output like:
Job# 1 staging 10 mins transaction 10 mins upload 10 mins total 30 mins

Each line is coming as a event in Splunk.
I know we have to use coalesce and streamstats, but I am new to splunk not able to figure out how to get to this level.

Thanks in advance.

[mayurr98]

Try this run anywhere search

| makeresults 
| eval data="{'job_no':'1','begin_build_time':'2019-08-15 11:00:00','event_type':'staging'} | {'job_no':'1','end_build_time':'2019-08-15 11:10:00','event_type':'staging'}" 
| makemv data delim="|" 
| mvexpand data 
| table data 
| rex field="data" "'job_no':'(?<job_no>[^\']+)','(begin_build_time':'(?<begin_build_time>[^\']+)|end_build_time':'(?<end_build_time>[^\']+))','event_type':'(?<type>[^\']+)'"| eval begin_build_time=strptime(begin_build_time,"%Y-%m-%d %H:%M:%S"),end_build_time=strptime(end_build_time,"%Y-%m-%d %H:%M:%S") 
| stats values(begin_build_time) as begin_build_time values(end_build_time) as end_build_time by type job_no
| eval time_diff_in_min=round((end_build_time-begin_build_time)/60)

In your env, you should try

index=<your_index>
| rex field=_raw "'job_no':'(?<job_no>[^\']+)','(begin_build_time':'(?<begin_build_time>[^\']+)|end_build_time':'(?<end_build_time>[^\']+))','event_type':'(?<type>[^\']+)'"| eval begin_build_time=strptime(begin_build_time,"%Y-%m-%d %H:%M:%S"),end_build_time=strptime(end_build_time,"%Y-%m-%d %H:%M:%S") 
| stats values(begin_build_time) as begin_build_time values(end_build_time) as end_build_time by type job_no
| eval time_diff_in_min=round((end_build_time-begin_build_time)/60)