How to separate sets of information with same field values without using transaction

[Traer001]

Hello,

I have log entries that look like this:

2021-06-21 16:36:14 Error Fix Success for issue submitted by user:14
2021-06-21 16:35:22 Error Found for Users:12,13,14
2021-06-21 16:21:11 Error Fix Success for issue submitted by user:19
2021-06-21 16:20:43 Error Found for Users:15,19,22,23
2021-06-21 16:07:38 Error Fix Success for issue submitted by user:14
2021-06-21 16:05:51 Error Found for Users:12,13,14

I want to be able to get the details (users, submitted_by user, and times) and calculate the durations of errors from when they are found to when they are fixed. I am trying to do this without using transactions. Currently my search finds the duration from 16:05:51 to 16:36:14 because the two sets of events have the same information. How can I rewrite my query (below) to get two different results for the error affecting users 12, 13 and 14?

My query:

index=INDEX host=HOST sourcetype=SOURCETYPE
| rex field=_raw "Error\sFound\sfor\sUsers:(?<users>.+)"
| rex field=_raw "Error\sFix\sSuccess\sfor\sissue\ssubmitted\sby\suser:(?<submitted_by_user>\d+)"
| where isnotnull(users) or isnotnull(submitted_by_user)
| sort 0 +_time -users
| filldown submitted_by_user users
| sort 0 -_time +users
| stats earliest(_raw) as earliest_raw latest(_raw) as latest_raw earliest(_time) as early_time latest(_time) as late_time by users submitted_by_user
| eval submitted_by_user=if(like(latest_raw, "%Found%"), "---", submitted_by_user)
| eval error_start=strftime(early_time, "%Y-%m-%d %H:%M:%S")
| eval error_end=if(submitted_by_user != "---", strftime(late_time, "%Y-%m-%d %H:%M:%S"), "---")
| eval duration=if(submitted_by_user != "---", tostring(late_time-early_time, "duration"), "---")
| eval users_involved=split(users, ",")
| eventstats count(users_involved) as user_count by earliest_raw
| fields - early_time late_time
| table users_involved, user_count, submitted_by_user, error_start, error_end, duration

[ITWhisperer]

Will something like this work (it is a little tricky with such a limited data set).

index=INDEX host=HOST sourcetype=SOURCETYPE
| rex field=_raw "Error\sFound\sfor\sUsers:(?<users>.+)"
| rex field=_raw "Error\sFix\sSuccess\sfor\sissue\ssubmitted\sby\suser:(?<submitted_by_user>\d+)"
| where isnotnull(users) or isnotnull(submitted_by_user)
| eval error_end=if(isnull(submitted_by_user),null(),_time)
| eval error_start=if(isnull(users),null(),_time)
| sort 0 -_time
| filldown submitted_by_user error_end
| where isnotnull(error_start)
| eval duration=error_end-error_start
| eval users_involved=split(users, ",")
| eval user_count=mvcount(users_involved)
| table users_involved, user_count, submitted_by_user, error_start, error_end, duration

[Traer001]

Thank you for the response @ITWhisperer . What you posted works for the limited data I provided. I'm sorry, I should have included more events with some different scenarios because it isn't working for some other situations. How would you rewrite it to work with a data set like this (a bit altered, so now there is an "Automatic" message that lets us know if the fix was successful or not)?

2021-06-21 16:36:14 Error Fix Success for issue submitted by user:14
2021-06-21 16:35:22 Error Found for Users:12,13,14
2021-06-21 16:22:35 Error Fix Automatic Success for issue submitted by user:34
2021-06-21 16:22:35 Error Fix Success for issue submitted by user:34
2021-06-21 16:21:11 Error Fix Automatic Success for issue submitted by user:19
2021-06-21 16:21:11 Error Fix Success for issue submitted by user:19
2021-06-21 16:20:43 Error Found for Users:15,19,22,23
2021-06-21 16:07:38 Error Fix Automatic Success for issue submitted by user:14
2021-06-21 16:07:38 Error Fix Success for issue submitted by user:14
2021-06-21 16:05:51 Error Found for Users:34,1,18
2021-06-21 16:04:38 Error Fix Automatic Failure
2021-06-21 16:04:38 Error Fix Success for issue submitted by user:14
2021-06-21 16:04:11 Error Found for Users:12,13,14

Note: 

If the set of data ends in an "Automatic Failure" and the next instance with the same user is not a "Found" message, then the duration should continue. For instance, in the above, the earliest instance for users 12, 13 and 14 starts at 16:04:11 and there is a "Failure" at 16:04:38, but then another "Error Fix Success" message for user 14 occurs but it isn't following a "Found" message, so the duration for these events should be from 16:04:11 to 16:07:38.

[ITWhisperer · Accepted solution]

Assuming that the error isn't actually fixed until the Automatic Success message, try this

| makeresults 
| eval _raw="2021-06-21 16:36:14 Error Fix Success for issue submitted by user:14
2021-06-21 16:35:22 Error Found for Users:12,13,14
2021-06-21 16:22:35 Error Fix Automatic Success for issue submitted by user:34
2021-06-21 16:22:35 Error Fix Success for issue submitted by user:34
2021-06-21 16:21:11 Error Fix Automatic Success for issue submitted by user:19
2021-06-21 16:21:11 Error Fix Success for issue submitted by user:19
2021-06-21 16:20:43 Error Found for Users:15,19,22,23
2021-06-21 16:07:38 Error Fix Automatic Success for issue submitted by user:14
2021-06-21 16:07:38 Error Fix Success for issue submitted by user:14
2021-06-21 16:05:51 Error Found for Users:34,1,18
2021-06-21 16:04:38 Error Fix Automatic Failure
2021-06-21 16:04:38 Error Fix Success for issue submitted by user:14
2021-06-21 16:04:11 Error Found for Users:12,13,14"
| multikv noheader=t
| fields _raw
| eval _time=strptime(_raw,"%Y-%m-%d %H:%M:%S")
| rex field=_raw "Error\sFound\sfor\sUsers:(?<users>.+)"
| rex field=_raw "Error\sFix\sAutomatic\sSuccess\sfor\sissue\ssubmitted\sby\suser:(?<submitted_by_user>\d+)"
| where isnotnull(users) or isnotnull(submitted_by_user)
| eval error_end=if(isnull(submitted_by_user),null(),_time)
| eval error_start=if(isnull(users),null(),_time)
| sort 0 _time
| eval user=split(users,",")
| mvexpand user
| eval user=coalesce(submitted_by_user,user)
| streamstats latest(error_start) as error_start latest(users) as users by user
| fieldformat error_end=strftime(error_end,"%Y-%m-%d %H:%M:%S")
| fieldformat error_start=strftime(error_start,"%Y-%m-%d %H:%M:%S")
| where isnotnull(error_end)
| eval duration=error_end-error_start
| eval users_involved=split(users, ",")
| eval user_count=mvcount(users_involved)
| table users_involved, user_count, submitted_by_user, error_start, error_end, duration