How to subtract two timestamps by session/ transaction_id?

[user33]

I have two events where in order to get a response time, I need to subtract the two timestamps. However, this needs to be grouped by "a_session_id" / "transaction_id." The two events I need are circled in red in the screenshot attached. I need those two events out of the three events. Every "a_session_id" has these three logs. source="/apps/logs/event-aggregator/gateway_aggregator_events.log" is always after source="/logs/apigee/edge-message-processor/messagelogging/gateway-prod/production/Common-Log-V1/14/log_message/gateway.json"

Please let me know if you need more information. Such as snippets on the SPL. Any assistance is much appreciated!

[bowesmana · Accepted solution]

You can do this

... data selection search...
``` Create a new common field from the transaction ids ```
| eval tx_id=coalesce(a_session_id, transaction_id)
``` Collect the times by the new transaction id ```
| stats values(timestamp) as start_time values(a_timestamp) as end_time by tx_id
``` Now calculate duration by parsing the two different time formats ```
| eval start=strptime(start_time, "%F %T.%Q")
| eval end=strptime(end_time, "%FT%T.%Q")
| eval duration=end-start

this will calculate the difference between the two timestamp fields in the two rows. If. you wanted to calculate the difference using the _time field, you could use range(_time) as duration in the stats command

[user33]

This looks great! One thing to note: 

• As another option, is there any way I can order stats by a bucket of time? (E.g. "| bucket timestamp span=1h@h") Taking the perc95 of the time?

THANK YOU!

[user33]

Actually, I figured it out. Thank you very much!!