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

vanakkam777 — Thu, 15 Aug 2019 22:05:44 GMT

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.

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

mayurr98 — Thu, 15 Aug 2019 23:22:36 GMT

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)

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

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

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