how to calculate the difference between two dates in transaction

[Mubarish]

I have a set of logs over which the transaction command is applied and some fields are extracted .Below is the sample result.

SessionID	Filename	StartDateTime	EndDateTime	Tranmission id	FileSize	status
814xxx		6/6/2014 4:15:12 AM	6/6/2014 4:15:12 AM		0	Completed
	Setxxxx.csv	6/6/2014 4:15:12 AM	6/6/2014 4:15:14 Am		213333
814xxx	ABC.ZIP.asc	6/6/2014 4:15:16 AM	6/6/2014 4:15:18 AM		0	Completed
	EFG.ZIP.asc	6/6/2014 4:15:18 AM	6/6/2014 4:15:20 AM		1359
	xxx.ZIP.asc	6/6/2014 4:15:20 AM	6/6/2014 4:15:22 AM		14940
		6/6/2014 4:15			69341
814xxx		6/6/2014 4:15:30 AM	6/6/2014 4:15:30 AM	0	0	Completed

   For each sessionid different files are transferred at different times. For each file there is a separate start and end time .When calculating the difference between the start and end time I am unable to calculate it for a sessionid that has multiple files transferred at different times, whereas there is no problem in calculating the difference between the start and end time for the sessionid that has only one file transmitted . 

The expected output is

SessionID	Filename	StartDateTime	EndDateTime	Tranmission id	FileSize	status
814xxx		6/6/2014 4:15:12 AM	6/6/2014 4:15:12 AM	0	0	Completed
	Setxxxx.csv	6/6/2014 4:15:12 AM	6/6/2014 4:15:14 Am	2	213333
814xxx	ABC.ZIP.asc	6/6/2014 4:15:16 AM	6/6/2014 4:15:18 AM	2	0	Completed
	EFG.ZIP.asc	6/6/2014 4:15:18 AM	6/6/2014 4:15:20 AM	2	1359
	xxx.ZIP.asc	6/6/2014 4:15:20 AM	6/6/2014 4:15:22 AM	2	14940
		6/6/2014 4:15			69341
814xxx		6/6/2014 4:15:30 AM	6/6/2014 4:15:30 AM	0	0	Completed

           Can anyone tell me how to calculate the difference between the start and end time here

[somesoni2 · Accepted solution]

Try this

your base search with transaction command | eval DiffInSecs=mvindex(EndDateTime,eventcount-1)-mvindex(StartDateTime,0)

Assuming for session with multiple file, your need to take the first StartDateTime and last EndDateTime and calculate the difference.

Updated

The best option here would be to calculate the different before the transaction command itself. After transaction command, both date fields have become multivalued fields, and there is no in-built command to to arithmetic on the same (unless you can develop your custom search command for the same).

Your base search | eval DiffInSecs=strptime(EndDateTime,"%m/%d/%Y %H:%M:%S")-strptime(StartDateTime,"%m/%d/%Y %H:%M:%S") | your transaction command..

(your can drop the strptime thing if they are already in epoch)
This should give a multivalued field DiffInSecs which will hold difference between corresponding StartDateTime and EndDateTime value.

[somesoni2]

See my updated answer.

[Mubarish]

Thanks but I need to calculate the time difference for individual file. How can i do?