Sumologic: How to get average time difference between two messages

Question

Having a set of logs like:

Log10:[requestId=2][taskId=C][message='End']
Log9: [requestId=2][taskId=C][message='Start']
Log8: [requestId=2][taskId=B][message='End']
Log7: [requestId=1][taskId=B][message='End']
Log6: [requestId=1][taskId=B][message='Start']
Log5: [requestId=1][taskId=A][message='End']
Log4: [requestId=2][taskId=B][message='Start']
Log3: [requestId=2][taskId=A][message='End']
Log2: [requestId=2][taskId=A][message='Start']
Log1: [requestId=1][taskId=A][message='Start']

First, I wanted to calculate the avg time each task takes to complete. I was able to that with transactionize:

* | concat(requestId,":",taskId) as transactionKey | transactionize transactionKey avg(_group_duration) group by taskId

Now, I'm willing to know how much time (avg) is happening between one task finishes and the next one is starting.

In this concrete example, my desired output would be:

((Log9 - Log8) + (Log4 - Log3) + (Log6 - Log5)) / 3

Any clue is appreciated.

Answer 1

Thanks to @chadoliver, he pointed me to the diff operator.

* | keyvalue auto | diff _messagetime by requestId | where message = "End" | avg(_diff) | ceil(_avg)

Answer 2

You may use regex, avg and group by functions to get aggregate results.

_sourceCategory="dev/test-app"
and "[Error]"
and "Error occurred"
| formatDate(_receiptTime, "yyyy-MM-dd") as date
| parse regex field=_raw "Error occurred. Exception:(?<message> \w.*)" nodrop
| replace(message,/my custom error message: ([0-9A-Fa-f\-]{36})/,"my custom error message") as replaceMessage
| parse regex field=_raw "\[Error](?<otherMessage> \w.*)" nodrop
| if (replaceMessage = "", otherMessage, replaceMessage ) as  consolidatedMessage
| if (length(consolidatedMessage)> 150,substring(consolidatedMessage,0, 150),consolidatedMessage) as  finalMessage
| count date, finalMessage
| transpose row data column finalMessage

https://www.youtube.com/watch?v=Nxzp7G-rUh8