How to write this subquery

Follow

Alex Rohde

• April 23, 2019 15:47
• Edited

My logs have two statements, linked by a trace-id that I need to connect. 

for example
"6:00:00 [abcdef] Job started by Alice"
"6:15:00 [jklmno] Job started by Bob"
"7:00:00 [abcdef] Job returned response 123"
"6:30:00 [jklmno] Job returned response 456"

What I'd like to see is a query showing:
"1:00 duration - by Alice - response 123"
"0:15 duration - by Bob - response 456"

I can't figure out how to do this with these subqueries... documentation and examples insufficient.

0

• Official comment

  

  Graham Watts

  • April 23, 2019 22:39
  • Edited

  Hey Alex,
  
  Transactionize can be very useful especially in this type of use case.
  
  Another way to get the duration is to use max() and min(), then do some math by each trace-id:
  
  | max(_messagetime), min(_messagetime) by trace-id
  | _max-_min as duration
  
  You may also want to do this over time to show on a dashboard, in the below case you would want to use some query logic to remove traces where the start time is before the timeslice start or the end time is after the timeslice end:
  
  | timeslice 10m
  | max(_messagetime), min(_messagetime) by trace-id, _timeslice
  | _max-_min as duration_ms | duration_ms/1000 as duration_sec
  | pct(duration_sec,50,95) by _timeslice
  
  Hope this helps!

  Permalink
• 

  Alex Rohde

  • April 23, 2019 21:46

  Somebody answered me in slack. The answer is not to use a subquery, but rather the "transaction" keyword

  1

  Permalink

Please sign in to leave a comment.