How to calculate the difference between two fields on two paired events matched via the contents of a third field?

[jamesofthedead8]

Trying to calculate out a "TransactionTime" time by pairing two events by one matching field (ECID) and then working the difference between two fields across the two fields (LoggingTime on the request then WritingTime on the response. Response/Request is the MessageType field).

Example events:

2019-10-27 22:04:34.0968 : response, 005_UiHXPHeX0001WE0009jP,0:1, 1572213874966, 2019-10-27 22:04:34.966, , osb/TestBindingQSService
2019-10-27 22:04:34.0873 : request, 005_UiHXPHe0001WE0009jP,0:1, 1572213874870, 2019-10-27 22:04:34.870, , osb/TestBindingQSService

[woodcock · Accepted solution]

Like this:

... | stats range(_time) AS duration1 min(LoggingTime) AS low max(WritingTime) AS high BY ECID
| eval duration = high - low
| fieldformat duration = tostring(duration)
| fieldformat duration1 = tostring(duration1)

[jamesofthedead8]

Thanks that seems to be working.

[richgalloway]

Please identify the fields in the example events and show the expected output.

[jamesofthedead8]

First event;

ECID=005_UiHXPHeX0001WE0009jP,0:1
LoggingTime=22:04:34.0968
WritingTime=22:04:34.966

Second event;

ECID=005_UiHXPHeX0001WE0009jP,0:1
LoggingTime=22:04:34.0873
WritingTime=22:04:34.870

Transaction time would be the difference between LoggingTime on event 2 (request) and WritingTime on event 1 (response).