combine events

[Mike6960]

Hi,
I am working on a search. The data consists of requests and answers. The answer and the request have the same unique ID (message.MessageId). Each request and answer has a message.RequestId. The answer also has an message.PreviousRequestId which is the initial message.RequestId from the request.
I need to monitor whether each request has received (or not) an answer and measure the time between them (using message.CreationDate). So far i have got this :

...search....
| transaction message.MessageId
| table message.MessageId message.CreationDate message.location message.RequestId message.PreviousRequestId
This reults in events combining the request and the answer like this:

What i would like (if possible) is that per messageId i get on line with the different values so i can calculate the difference

[somesoni2 · Accepted solution]

The transaction command would automatically calculate the difference in the field duration. So adding that to your table command would do for you here.

But transaction commands are really expensive. Since, your transactions have just two events with no complicated conditions, you can try this more efficient alternative as well.

...search.... 
| eval epochCreationDate=strptime('message.CreationDate',"%Y-%m-%dT%H:%M:%S.%N%z")
| stats values(message.CreationDate) as CreationDate values(message.location) as Location values(message.RequestId) as RequestId values(message.PreviousRequestId) as PreviousRequestId range(epochCreationDate) as Duration by message.MessageId 

[Mike6960]

Hi, this gives me duration but ik still get the results as i got them earlier (like in the screenshot) i still miss both the creationdates

[somesoni2]

The first part of stats values(message.CreationDate) as CreationDate should give you both the CreationDate values, unless both are same. you should be getting duration=0 in that case. If that's what happened, then use list(message.CreationDate) as CreationDate instead in your stats.

[Mike6960]

If I use:
| eval epochCreationDate=strptime('message.CreationDate',"%Y-%m-%dT%H:%M:%S.%N%z")
| stats list(message.CreationDate) as CreationDate |stats values(message.location) as Location values(message.RequestId) as RequestId values(message.PreviousRequestId) as PreviousRequestId range(epochCreationDate) as Duration by message.MessageId

I get 'no results'

[somesoni2]

It should be included in existing stats, not a new one. Like this:

...search.... 
 | eval epochCreationDate=strptime('message.CreationDate',"%Y-%m-%dT%H:%M:%S.%N%z")
 | stats list(message.CreationDate) as CreationDate values(message.location) as Location values(message.RequestId) as RequestId values(message.PreviousRequestId) as PreviousRequestId range(epochCreationDate) as Duration by message.MessageId 

[Mike6960]

Great, it works. Ony thing is that 'list' has a maximum.