grouping events based on start and end

[venky1544]

Hi All

i have a below data 

Date	Orginaldate	jobid	process_name	Messge_text
14-02-2020 T11:30:00	14-02-2020 T11:25:00	a1234	testprocess1	process start
14-02-2020 T11:45:00	14-02-2020 T11:35:00	a1236	testprocess2	process start
14-02-2020 T12:00:00	14-02-2020 T11:47:00	a1234	testprocess1	process ends
14-02-2020 T12:15:00	14-02-2020 T11:50:00	a1235	testprocess3	process start
14-02-2020 T12:30:00	14-02-2020 T12:17:00	a1235	testprocess3	process ends
14-02-2020 T12:45:00	14-02-2020 T12:35:00	a1236	testprocess2	process ends
14-02-2020 T13:00:00	14-02-2020 T12:50:00	a1237	testprocess4	process start
14-02-2020 T13:15:00	14-02-2020 T13:05:00	a1237	testprocess4	process ends

i want to group the events jobid  based on original date column and not the _time or the first column  and want the below output 

Orginaldate	jobid	processname	start msg	end_msg	duration
14-02-2020 T11:25:00	a1234	testprocess1	process start	process ends	1320
14-02-2020 T11:35:00	a1236	testprocess2	process start	process ends	3600
14-02-2020 T11:50:00	a1235	testprocess3	process start	process ends	1620
14-02-2020 T12:50:00	a1237	testprocess4	process start	process ends	900

i have tried the timestamp into epoch and then grouped the event but im not able separate out the two start and endtime

[venky1544]

Hi Vikas 

I tried the above commands and it didn't worked for me but i tweaked the solution by shuffling the rex extractions at the end and it worked for me not sure if its a right approach below is my modified query 

sourcetype="csv"|transaction jobid startswith=eval(Messge_text="process start") endswith=eval(Messge_text="process ends") |eval duration= tostring(duration,"duration") |rex field=Messge_text "(?<start_message>process start)" |rex field=Messge_text "(?<end_message>process ends)"|table Orginaldate,jobid,start_message,end_message,duration

[vikas_gopal · Accepted solution]

One way is with transaction 

sourcetype="csv" |transaction jobid startswith=eval(Messge_text="processstart") endswith=eval(Messge_text="processends") |eval duration= tostring(duration,"duration") |table Orginaldate,jobid,Messge_text,duration

Because we know transaction command is a little expansive command so we can use fields as well to only focus on respective fields

sourcetype="csv" |fields Orginaldate,jobid,Messge_text|transaction jobid startswith=eval(Messge_text="processstart") endswith=eval(Messge_text="processends") |eval duration= tostring(duration,"duration") |table Orginaldate,jobid,Messge_text,duration

In case you are extracting message from a fields as a new field then you can use below 

sourcetype="csv" |rex field=Messge_text "(?<actual_message>processstart)" |rex field=Messge_text "(?<actual_message>processends)"|transaction jobid |eval duration= tostring(duration,"duration") |sort Orginaldate|table Orginaldate,actual_message,jobid,duration