1

I have a query that returns a number of results that show the start and end of transactions in the logs. So for every transaction there's a "start" and an "end" log entry.

fields @timestamp, @message 
| parse @message "*: *" as logContext, logMessage
| filter (logContext like "Transaction start"
       or logContext like "Transaction end")
| sort @timestamp asc

An example log might look like this:

Transaction start: Message 1 received
Transaction processing: Message 1 identified as "GetData"
Transaction processing: Message 1 "GetData" extracting fields
Transaction processing: Message 1 "GetData" routing - none
Transaction end: Message 1 commit
...
...
Transaction start: Message 49085 received
Transaction processing: Message 49085 identified as "DataResponse"
Transaction processing: Message 49085 "DataResponse" extracting fields
Transaction processing: Message 49085 "DataResponse" routing - return to sender
Transaction end: Message 2 commit

I want to only return the start of the first and end of the last transactions in the logs so that I can measure the time between them based on @timestamp.

2021-06-14T15:25:00, Transaction start: Message 1 received
2021-06-15T09:45:00, Transaction end: Message 49085 commit

I know I can use limit to return the first OR last depending on sort order, but not sure how to return both.

DaBozUK
  • 590
  • 1
  • 8
  • 24

1 Answers1

0

You can do this using the stats command, along with the earliest and latest functions, like this:

stats
  earliest(@timestamp) as firstTimestamp,
  latest(@timestamp) as lastTimestamp,
  lastTimestamp - firstTimestamp as durationMs

So, for the specific query used in the question, you could do this:

fields @timestamp, @message 
| parse @message "*: *" as logContext, logMessage
| filter (logContext like "Transaction start"
       or logContext like "Transaction end")
| stats
    earliest(@timestamp) as firstTimestamp,
    latest(@timestamp) as lastTimestamp,
    lastTimestamp - firstTimestamp as durationMs
vaughandroid
  • 4,315
  • 1
  • 27
  • 33
  • Hello ! Where can I find the documents with all or most of the syntax like these, to query out the required fields? – Asish Jun 01 '23 at 05:28
  • Also, how can I learn more about parsing message? I am still not able to figure that out – Asish Jun 01 '23 at 05:28