How to find duration of repeating events

[Traer001]

Hi all,

I am trying to get the duration of the starting found error based on the affected users and the last fail/success message. For instance, if I have events like this:

2021-06-17 13:15:13 Error Resolve Status Success for Issue submitted by User:132
2021-06-17 13:15:12 Error Resolve Success for Users:131,132,133 submitted_by:132
2021-06-17 13:13:15 Error Resolve Status Failed
2021-06-17 13:13:14 Error Found, Users:131,132,133 affected
2021-06-17 13:13:14 Error Resolve Success for Users:166,167,168 submitted_by:166
2021-06-17 13:12:31 Error Resolve Status Failed
2021-06-17 13:12:31 Error Resolve Success for Users:166,167,168 submitted_by:166
2021-06-17 13:11:47 Error Found, Users:166,167,168 affected

I want to be able to find the duration from 13:11:47 to 13:13:15 for the users 166, 167 and 168, and I want to get the duration from 13:13:14 to 13:15:13 for users 131, 132 and 133.

I was originally going to use transactions, but I don't think that would work well here. So how can I write my query to get the durations I'm looking for based on the users affected?

Thanks in advance!

[bowesmana]

There doesn't appear to be anything in this event that can correlate it t the original at 13:11:47

2021-06-17 13:13:15 Error Resolve Status Failed

How do you know that applies to the message at 13:11:47 and not the one at 13:13:14?

Can you explain the rules that apply to what you are trying to achieve?

[Traer001]

Hi @bowesmana ,

Yeah, there isn't anything to make those final success/fail messages unique unfortunately. The general pattern is that there needs to be an initial "Error Found" message, an "Error Resolve Success" message after that, and ending with either a "Status Success" or "Status Failed" message. If the ending status is "Failed", the "Error Resolve Success" and "Status" messages may repeat like shown. Generally, the "Status" messages will occur at the same time as the "Resolve Success" messages or within a second.

So in this case, the "Failed" message at 13:13:15 does not correlate to the "Error Found" message at 13:13:14 because there hasn't been an "Error Resolve Success" message between them.